469,929 Members | 2,050 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,929 developers. It's quick & easy.

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.
Nov 16 '05 #1
3 2106
"VMI" <vo******@yahoo.com> wrote in
news:Of**************@TK2MSFTNGP10.phx.gbl:
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.
-------------
C.R. Timmons Consulting, Inc.
http://www.crtimmonsinc.com/
Nov 16 '05 #2
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.
--
Justin Rogers
DigiTec Web Consultants, LLC.
Blog: http://weblogs.asp.net/justin_rogers

"Chris R. Timmons" <crtimmons@X_NOSPAM_Xcrtimmonsinc.com> wrote in message
news:Xn**********************************@207.46.2 48.16...
"VMI" <vo******@yahoo.com> wrote in
news:Of**************@TK2MSFTNGP10.phx.gbl:
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.
-------------
C.R. Timmons Consulting, Inc.
http://www.crtimmonsinc.com/

Nov 16 '05 #3
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

"Justin Rogers" <Ju****@games4dotnet.com> wrote in message news:<eQ**************@TK2MSFTNGP11.phx.gbl>...
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.
--
Justin Rogers
DigiTec Web Consultants, LLC.
Blog: http://weblogs.asp.net/justin_rogers

"Chris R. Timmons" <crtimmons@X_NOSPAM_Xcrtimmonsinc.com> wrote in message
news:Xn**********************************@207.46.2 48.16...
"VMI" <vo******@yahoo.com> wrote in
news:Of**************@TK2MSFTNGP10.phx.gbl:
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.
-------------
C.R. Timmons Consulting, Inc.
http://www.crtimmonsinc.com/

Nov 16 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by Darrel | last post: by
4 posts views Thread by darrel | last post: by
4 posts views Thread by aqua404 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.