Good Query for Paging?

Hi,

I am wondering what is the suggested query to use when I want to page through a bunch of data. I figure this is a pretty common situation so there should be some good ways to do this.

Basically I have a fixed page size and everytime a new one is requested, I just want to get those records. So if my page size is 10, for the first one I want to get records 1-10 of an ordered set, for page 2 I want to get records 11-20, etc.

The problem is I can only see "TOP" as an option, but that gives me the *first* n records. What if I want a set of records in the middle of the set?

I am also thinking some kind of subquery with an identity column, and then selecting based on the identity column may do the trick.

I would appreciate any any good solutions for this!

Thanks.select top 10 *
from (select top 20 * from tbl order by id) as a
order by id desc

For a variable page do it in dynamic sql.

or temp table solution - could use table variable)
create table #a (id int identity(1,1), ...)
set rowcount = @.maxrecs
insert #a select ...
set rowcount 0
select top 10 * from #a order by id desc
drop table #a