Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2003
    Posts
    6

    Unanswered: 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.

  2. #2
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •