Paul Maddox

A poster by the name of Stripe-man has come up with a really simple way of paginating data in SQL Server.  This has always been easy with MySQL using the LIMIT keyword, which is missing from SQL Server 2000.  (SQL Server 2005 added a row count similar to Oracle's, I believe.)

Here is the template:

SELECT TOP <PAGESIZE> * FROM task_log WHERE id NOT IN (SELECT TOP <PAGEOFFSET> id FROM task_log ORDER BY ID) ORDER BY ID

So, for example, to display 10 items from 51-60 we would do:

SELECT TOP 10 * FROM task_log WHERE id NOT IN (SELECT TOP 50 id FROM task_log ORDER BY ID) ORDER BY ID

What this essentially says is: show me the top ten results that do not appear in the top fifty results.

PHPBuilder.com - View Single Post - Pagination with PHP / MsSQl not MySQL!

# posted by Paul Maddox @ 1:04 AM