I read this (http://www.phpbuilder.com/forum/archives/5/2001/08/1/16798) post and was astonished at the method used to perform a LIMIT/OFFSET functionality in Microsoft databases which utilize the TOP clause to limit returned records.
The method of using a cursor should be frowned upon as it has potential to drop resources available on the machine, especially in the event you forget to close your cursor when you are done with it.
I have written a small article which I have posted on some other sites, and now i feel the need to post the article here too.
This method has been proven to out perform the cursor method mentioned in the article listed above, and is much more scalable and does not require the use of a stored procedure. It is also a valid method for Microsoft Access 2000 and 2002 (Unfortunatly versions of Access before 2000 did not support derived tables, limiting this method to its successors).
-- BEGIN ORIGINAL ARTICLE --
A common misconception is that Microsoft SQL Server
and Microsoft Access 2000 and up can not perform the
same functionality as MySQL or PostgreSQL (and some
other common rdbm systems).
This misconception is that Microsoft does not support
the common feature known as LIMIT. This feature is
extremely helpful when designing web databases in
which you need to 'page' through your records.
A MySQL or PostgreSQL query for such a feature may
look similiar to the below:
select * from messages order by DateStamp LIMIT 100, 150
The above query would return 100 records starting at offset
150. This is helpful as you can increment the offset and
generate a 'paging' system.
How do you perform the same feature in Microsoft databases?
Good question ...
Heres an example ... for those who are just here to get the syntax:
select from (select top LIMIT from (
select top (LIMIT+OFFSET) * from messages order by DateStamp desc) as t1)
order by DateStamp asc) as t2
order by DateStamp desc
This query will return LIMIT records, from offset OFFSET. Note (LIMIT+OFFSET) must be evaluated ahead of time, you can not perform calculations in a TOP clause. This is possible either client-side, or in a stored procedure server-side.
It appears rather odd, simply because it uses a technique known as derived tables. Derived tables are simply 'from' clauses which are obtained from sub-select queries, rather then views or phsyical tables.
To break the statement down a bit, let's look at the inner most (core) of the query.
select top 250 * from messages order by dateStamp desc
That will select 250 records from messages, ordered by the DateStamp field, in a descending fashion.
Now what we do is we select 100 records from that, and we end up with the last 100 records of the query, we do this with:
select top 100 * from (...) as t1 order by DateStamp asc
That portion of the query then returns 100 records, ordered by DateStamp in ascending order. This gives us a bit of freedom, now our records are in cronological order. They were obtained in reverse cronological order though ...
Now we re-order the records to put them back into reverse cronological order, simply to show the newest record first, and the oldest record last ...
we do that by using the following:
select * from (...) as t2 order by DateStamp desc
Now you have a complete paging query ...
