Tech Off Thread

9 posts

Forum Read Only

This forum has been made read only by the site admins. No new threads or comments can be added.

How to retreive a range of records in SQL

Back to Forum: Tech Off
  • User profile image
    Shrage

    I want to pass CurentPage and ItemsPerPage to a qauery and it should result in only that page of records

    I know i can use SELECT TOP x  to select x records, but i ned for example from the 10th record till the 20th.

  • User profile image
    Maurits

    Shrage wrote:
    I want to pass CurentPage and ItemsPerPage to a qauery and it should result in only that page of records

    I know i can use SELECT TOP x  to select x records, but i ned for example from the 10th record till the 20th.


    There's no sensible, easy, way to do this in current versions of SQL server, sorry.

    See this post for discussion
    See this wiki page for another way


  • User profile image
    Shrage

    Oh wow what a discussion, i was thinking the TOP 15 ORDER BY FIELD ASC (TOP 5 ORDER FIELD DESC)

    Creating a temp table each time i run the query? don't like that.

    A Limit function would be great, but i see most people here don't even recognize it's value, they use the pagination in ADO, but what a waste of valuble bandthwith to get a result of 3000 row, just to see a page of 15 records.

    The WHERE ID > LowerBound and ID<UpperBound is completly wrong, becasue what if you have a GUID for an ID?

  • User profile image
    Shrage

    Ha ha i have an idea,

    SELECT TOP @ItemsPerPage *
    WHERE ID NOT IN
        (SELECT TOP @CurentPage*@ItemPerPage)

    Notice the NOT IN

  • User profile image
    MrWilder

    oooh, yes an issue indeed.

    I have used both of the following methods to return paged results without resorting to a temporary table.

    Let me know if this helps:

    http://weblogs.sqlteam.com/jeffs/archive/2004/03/22/1085.aspx

    http://www.windowsitpro.com/Articles/Index.cfm?ArticleID=40505&DisplayTab=Article

    word.

  • User profile image
    Shrage

    It's all good and fine, but when i have a complex query it gets trippled just to to get a range of rows? I'm very mad. Perplexed

  • User profile image
    footballism

    Shrage wrote:

    Ha ha i have an idea,

    SELECT TOP @ItemsPerPage *
    WHERE ID NOT IN
        (SELECT TOP @CurentPage*@ItemPerPage)

    Notice the NOT IN

    This is not the best solution in terms of performance, but it actually is the easiest one:p

  • User profile image
    Maurits

    Shrage wrote:
    It's all good and fine, but when i have a complex query it gets trippled just to to get a range of rows? I'm very mad.


    Direct your anger productively... write a nicely-framed request to the SQL Server team to request that SQL99 compliance be made a priority in the next version

  • User profile image
    Tensor

    Pagination will be possible in SQL Server 2005 allthough not using the LIMIt keyword, which will not be supported.

Conversation locked

This conversation has been locked by the site admins. No new comments can be made.