MS SQL Server pagination

[goldbug]

Just a quick question:

How would one do result pagination in SQL Server (2000), similar to the 'LIMIT' method using MySQL??

I've tossed around the idea of using a temporary table, then deleting TOP n rows, then returning the resulting TOP n rows from the temp table (in a stored procedure of course). Also discussed possibility of using cursors.

Just wondering if anyone knows of a simple and/or elegant way of getting one 'page' of records in a single query, or something cleaner than what I described above.

[Wynder]

Not sure with MS SQL, but with Oracle 8i and above this works:

select from ( select a., rownum rnum from ( OUR QUERY HERE ) a where rownum <= MAXNUM ) where rnum >= MINNUM

Where OUR QUERY HERE, you put your full SQL query. This is prolly completely off base, but thought I'd chime in. 🙂

[BlueMeteor]

no method like 'limit' in ms-sqlserver

But you can try this

select IDENTITY(int, 1,1) AS ID_Num,* into #temp from table_name

select * from #temp where ID_Num between 10 and 20

hope that help

btw:MSDN maybe make more help