Select next 10 rows ?

[PW]

I am writing an ASP application which sits on top of an Access database
which contains a table with over 100K records.

I need to display approx 10 records at a time. Therefore I dont want to
"select * from table" and then page, as the initial "select" statement will
be very slow to perform.

Is there such a thing as "SELECT NEXT 10 * FROM TABLE" or something similar,
where I can just retrieve the initial 10 records, and subsequently fetch the
next 10 when the user clicks a "next" button?

 

[Tom Ellison]

Dear PW:

Selecting the second 10 can be done with:

SELECT * FROM (
SELECT TOP 10 *
FROM (SELECT TOP 20 *
FROM YourTable
ORDER BY ColumnName)
ORDER BY ColumnName DESC)
ORDER BY ColumnName

By reversing the sort, you get the bottom 10 of the top 20. I then sort
them in the original sequence.

In selecting 10 rows unambiguously, you would need a unique sort order.

Tom Ellison