If you love 'acrobatic' SQL this is a simulation of paging with an Access MyTable table with an
Id field as primary key:
SELECT MyTable.*
FROM MyTable
WHERE MyTable.Id In
(
SELECT TOP 10 A.Id
FROM [
SELECT TOP 30 MyTable.Id
FROM MyTable
ORDER BY MyTable.Id
]. AS A
ORDER BY A.Id DESC
)
ORDER BY MyTable.Id
The SQL statement queries the top 30 records, reverses the order, queries the top 10, and then selects the rows from the table that match, sorting in forward order again.
So it extracts the records from 21 to 30 from the table.
A problem is that the TOP clause doesn't accept parameters, so you must build the statement inserting the offset value (replacing '30') concatenating strings (pay attention to SQL Injection).
GmGregori
Contributor
5564 Points
749 Posts
Re: Webmatrix, Access Database and SQL syntax
Feb 14, 2012 07:32 AM|LINK
If you love 'acrobatic' SQL this is a simulation of paging with an Access MyTable table with an Id field as primary key:
SELECT MyTable.* FROM MyTable WHERE MyTable.Id In ( SELECT TOP 10 A.Id FROM [ SELECT TOP 30 MyTable.Id FROM MyTable ORDER BY MyTable.Id ]. AS A ORDER BY A.Id DESC ) ORDER BY MyTable.IdThe SQL statement queries the top 30 records, reverses the order, queries the top 10, and then selects the rows from the table that match, sorting in forward order again.
So it extracts the records from 21 to 30 from the table.
It's not mine, and you can see more details at http://stackoverflow.com/questions/8627032/ms-access-limit-x-y.
A problem is that the TOP clause doesn't accept parameters, so you must build the statement inserting the offset value (replacing '30') concatenating strings (pay attention to SQL Injection).