Getting bottom N records from DB table to Datatable

[VMI]

How can I get the bottom N records from an Access table and store them in my
DataTable?
For example, in my Access table with 2000 records, if I want to display
records 151-200 (with ID as PK), my query
would be:

"select top 50 * from audit where ID > 150 order by ID asc"

Since ID is type AutoNumber, I'll see records 151-200. But once I'm
displaying them, how can I go back to seeing 101-150? I tried:

"select top 50 * from audit where ID <= 150 order by ID desc" and it'll show
me the correct data, but in the wrong order (from 150-101).

Thanks.

 

[Chris R. Timmons]

How can I get the bottom N records from an Access table and
store them in my DataTable?
For example, in my Access table with 2000 records, if I want to
display records 151-200 (with ID as PK), my query
would be:

"select top 50 * from audit where ID > 150 order by ID asc"

Since ID is type AutoNumber, I'll see records 151-200. But once
I'm displaying them, how can I go back to seeing 101-150? I
tried:

"select top 50 * from audit where ID <= 150 order by ID desc"
and it'll show me the correct data, but in the wrong order (from
150-101).

To see records 101-150, can't you just do "select top 50 * from audit
where ID > 100 order by ID asc"?


Chris.

 

[Justin Rogers]

Okay guys, issuing a random Id, such as 100 when helping this guy
really isn't going to help. We all know that database primary keys
can disappear and leave holes, that he might be doing an additional
filter on top of the entire thing, or all sorts of other issues. The only
real way to do this is very expensive, and I'm sure that is what he
is trying to avoid.

You have:
1. Get the rowcount
2. Subtract your desired final records
3. Run the query with this row value.
4. Run the query using a not int clause with the ids retrieved in the previous
step.

Super expensive. And if you need to do this arbitrarily then it won't be a top
N at all, but a ROWCOUNT set, which is much slower than a top N in many
cases, especially on partially sorted or almost sorted heaps. If the table is
indexed,
then or not often update, then eh, you can write optimizations for the entire
process.

 

[Felix]

The following should work and is quite cheap:

SELECT *
FROM
(
SELECT TOP 50 * FROM audit
ORDER BY ID DESC
) T1
ORDER BY ID

Though, I would perform a simple query and do the sorting in a DataView.

-Felix