Fetch rows given row nos

[Prashantha Weerakoon]

hello there

In my application i have a table of 100 000 records , and i need to fetch
them 10000 each . for example first fetch i need to get 1-10000 records ,
second fetch 10001 -20000 records to the record set.
What can be the acess query to get something like this

my client program is a java program..

Here i only require is SQL query..

thanks in advance
PRash

 

[Michel Walsh]

Hi,


With Jet? SELECT TOP 10000 ... ORDER BY pk can supply the first batch.
For the next batch, try a to add a WHERE clause

SELECT TOP 10000 .... WHERE pk> pk10000 ORDER BY pk


where pk10000 is the pk value of the last record in the first batch.


With SQL Server 2005 (Yukon), you can make use of ROW_NUMBER() OVER:

SELECT *
FROM ( SELECT ROW_NUMBER() OVER (ORDER BY pk) As rowNumber, *
FROM myTable ) As a
WHERE rowNumber BETWEEN 10001 AND 20000




Hoping it may help,
Vanderghast, Access MVP

 

[Prashantha Weerakoon]

I am using ODBC connection with ADO connection.
so this query throws an error when i am trying to complile it ....


rgds
Prash

 

[Michel Walsh]

Hi,

Not all SQL engines are equal and not all SQL engines support TOP N. Jet
does, MS SQL Server 2000 does. You may ask to a newsgroup dealing with your
SQL engine. If that engine *is* Jet or MS SQL Server 2000, can you post the
complete SQL statement.


Hoping it may help,
Vanderghast, Access MVP