Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820

    Question Unanswered: Selecting a sub set of results

    Hi hopeful this will be a quick one, i have a query that returns a hell of a lot of records not if i do top 1000 i can get the first thousand, is there a simple way to then get the second thousand and the third?

    because i need to process the data but with it going all in one go it's being a bit of a resource hog.


    this is best i've come up with so far but I'm hoping for a better method
    Code:
    SELECT   TOP 10 *
    FROM      testtable
    WHERE    id NOT IN
       (SELECT TOP 10 id
       FROM     testtable)
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    What version of SQL Server?

    Basically you can do this with pagination techniques. You could try googling this.

  3. #3
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    knew i forgot something sorry

    stuck with 2000 currently
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    what you want is something like server side pagination. you are going toe need a temp table with the identity function and then you are going to pass it a parameter for the set of 1000 you need and then the where clause of the temp table looks something like so...

    Code:
    WHERE RowNum Between ((@PageNumber * 1000) - 999) AND (@PageNumber * 1000)
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    wouldn't you have to basically re-load the entire temp table on every call ?

    whoa
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Quote Originally Posted by Thrasymachus
    what you want is something like server side pagination. you are going toe need a temp table with the identity function and then you are going to pass it a parameter for the set of 1000 you need and then the where clause of the temp table looks something like so...

    Code:
    WHERE RowNum Between ((@PageNumber * 1000) - 999) AND (@PageNumber * 1000)
    Or build a permanent table and use a guid as a session identifier. Whn you populate the table, you have the guid and the sorted key of the prev and next pages. Another job would run every 15 - 30 minutes to remove entries based on time.

    -- This is all just a Figment of my Imagination --

  7. #7
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    Quote Originally Posted by Thrasymachus
    what you want is something like server side pagination. you are going toe need a temp table with the identity function and then you are going to pass it a parameter for the set of 1000 you need and then the where clause of the temp table looks something like so...

    Code:
    WHERE RowNum Between ((@PageNumber * 1000) - 999) AND (@PageNumber * 1000)
    that is going to be dire, it'll require the entire query to dump it's results in a table and then need a separate query to process is and it'll massively increase the size of the database.

    unless I've missed something i don't see any advantage over my initial idea

    Code:
    Declare @FirstRow int
    Set @FirstRow = @PageSize * @PageNumber
    SELECT   TOP @PageSize *
    FROM      <Table>
    WHERE    id NOT IN
       (SELECT TOP @FirstRow id
       FROM     <Table>)
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    If this is SQL 2K then that is invalid syntax

  9. #9
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    Quote Originally Posted by pootle flump
    If this is SQL 2K then that is invalid syntax
    that'll teach me for writing code on the fly
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  10. #10
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    okay might not have been my error

    http://www.codeproject.com/KB/aspnet/PagingLarge.aspx
    give this as a method of doing it, and says it's for SQL 2000:
    Code:
    SELECT ... FROM Table WHERE PK IN 
        (SELECT TOP @PageSize PK FROM Table WHERE PK NOT IN
            (SELECT TOP @StartRow PK FROM Table ORDER BY SortColumn)
        ORDER BY SortColumn)
    ORDER BY SortColumn
    and thats what i combined with my initial thought for that code
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  11. #11
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Quote Originally Posted by r937
    wouldn't you have to basically re-load the entire temp table on every call ?

    whoa
    got something better? it solved some time out and out of memory problems i had once because they were pulling all of the results back and paginating in the application code. The queries went from 30 plus seconds to 1 or 2. Not ideal, but it is not commercial software. I can live with 1 or 2 seconds in a a back office environment on a 4 or 5 year old server that also has the IIS server on it.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Nope - parameterised TOP statements only came in for 2005. You can concatenate a parameter value into some dyncamic SQL in 2K of course.

  13. #13
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    Quote Originally Posted by pootle flump
    Nope - parameterised TOP statements only came in for 2005. You can concatenate a parameter value into some dyncamic SQL in 2K of course.
    read my mind
    Code:
    Set @FirstRow = @PageSize * @PageNumber
    exec ('SELECT TOP ' + @PageSize  + ' * FROM testtable WHERE id NOT IN (SELECT TOP ' + @FirstRow + ' id FROM testtable)')
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Thrasymachus
    got something better?
    yep, carry a PK value around with you

    then the database call changes from "gimme rows 900 to 1000 from da query" to "gimme da next 100 rows after PK=4012" and then you would use ROWCOUNT with WHERE PK>4012

    simple, yes?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Oooh - something popped into my head - Don't forget to cast it to an character data type

Posting Permissions

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