Selecting X records

[penina]

Hello,

I have a database with auto index as the key.
Its' max size is 50000 records.
I want to get the first 10000 records and than the second 10000 records and so on.

How do I do that?
I can't ask from index X to index Y because I don't know X and Y that will give me exactly 10000 records.
I can get all indexes of the table to an arrray and find the index that is in the 10000 spot in the array.

Is there an other way?

[antares686]

As long as there is an column with a consective autonumber you are using you can do

Option 1

SELECT * FROM
{
SELECT TOP 10000 * FROM
{
SELECT TOP n * FROM tblX ORDER BY ColAutoNum
} AS IQ
ORDER BY ColAutoNum DESC
} AS OQ
ORDER BY ColAutoNum

Where n represents the highest position to return.

Option 2

SELECT * FROM tblX OQ WHERE (SELECT (COUNT(*) + 1) AS CNT FROM tblX IQ WHERE IQ>ColAutoNum < OQ.ColAutoNum) BETWEEN n1 AND n2

where n1 is the starting position and n 2 is the ending.

[M Owen]

Another means is by utilizing the CacheSize of the recordset ...