Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 8 of 8
  1. #1
    Regular Coder
    Join Date
    Jun 2006
    Location
    UK
    Posts
    911
    Thanks
    302
    Thanked 2 Times in 2 Posts

    SQL Server 2000 Row Limits

    i All,

    I have 20,000 records in my database

    Is there any way i can run a sql query that can fetch the first 5000 row, then the next 5000 row and so on?

    I do not want to fetch 20,000 rows at a time.


    Plz help.


    THanx

  • #2
    Regular Coder
    Join Date
    Mar 2007
    Posts
    505
    Thanks
    1
    Thanked 19 Times in 19 Posts
    You would do this by using WHERE statements in your SQL.

    One could also use LIMITs, but it's not necessary.

    Page 1:

    SELECT yourFields FROM yourTable WHERE ID > 5000

    Page 2:

    SELECT yourFields FROM yourTable WHERE ID BETWEEN 5000 AND 10000

    Page 3...4... and so on.

    To do this programmatically, set variables (either by session or by Querystring) telling your SQL string which limits of the WHERE statement to include.

    FOR EXAMPLE:
    Code:
    <%
    if Request.Querystring("LL") = "" then 
       LL = "0"  'LL = lower limit
    else
       LL = request.querystring("LL")
    end if
    '
    if Request.Querystring("UL") = "" then 
       UL = "5000"
    else
       UL = request.querystring("UL")
    end if
    '
    '
    rs.open "SELECT yourFields FROM yourTable WHERE ID BETWEEN " & LL & " AND " & UL & " ORDER BY ID ASC;", conn
    
    %>
    To say my fate is not tied to your fate is like saying, 'Your end of the boat is sinking.' -- Hugh Downs
    Please, if you found my post helpful, pay it forward. Go and help someone else today.

  • #3
    New Coder
    Join Date
    May 2007
    Location
    SF, CA
    Posts
    55
    Thanks
    0
    Thanked 4 Times in 4 Posts
    Have you looked at using cursors. I believe they have been available since SQL Server 6.0

    semper fi...

  • #4
    Senior Coder TheShaner's Avatar
    Join Date
    Sep 2005
    Location
    Orlando, FL
    Posts
    1,126
    Thanks
    2
    Thanked 40 Times in 40 Posts
    Quote Originally Posted by Daemonspyre View Post
    One could also use LIMITs, but it's not necessary.
    SQL Server does not utilize the LIMIT keyword. Other databases, like MySQL and PostGreSQL, do, however. If SQL Server did utilize the LIMIT keyword, that would definitely be the ideal way to paginate your results.
    Quote Originally Posted by Daemonspyre View Post
    SELECT yourFields FROM yourTable WHERE ID > 5000
    What if the OP isn't using an incremental integer ID field? Or records have been deleted and so only 4000 records exist between 1 and 5000? I have to disagree on using this method! Sorry, hehe.

    Cancer10, if you are using SQL Server 2005 or above, you may follow this method:
    http://blogs.msdn.com/sqlserver/arch...ql-server.aspx

    This is another pagination solution, although a bit complex:
    http://www.4guysfromrolla.com/webtech/042606-1.shtml

    And last, but not least, there is the TOP keyword technique (implemented from 7.0 and on):
    Code:
    --Grabbing records 5001 - 10000
    SELECT *
    FROM 
     (
      SELECT TOP 5000 field1, field2, etc.
      FROM 
       (
        SELECT TOP 10000 field1, field2, etc.
        FROM table
        ORDER BY field1 ASC
       ) AS tmptbl1
      ORDER BY field1 DESC
     ) AS tmptbl2 
    ORDER BY field1 ASC
    -Shane
    Last edited by TheShaner; 07-02-2007 at 10:45 PM.

  • #5
    Regular Coder
    Join Date
    Mar 2007
    Posts
    505
    Thanks
    1
    Thanked 19 Times in 19 Posts
    Shane --

    You caught me -- I exclusively use MySQL, ergo, I was not familiar with the fact that MS SQL Server didn't use ANSI Standard SQL. (What?! MS not use standards??? No....)

    My example was one of simplicity, and it's not always the best solution.
    To say my fate is not tied to your fate is like saying, 'Your end of the boat is sinking.' -- Hugh Downs
    Please, if you found my post helpful, pay it forward. Go and help someone else today.

  • #6
    Regular Coder
    Join Date
    Jun 2006
    Location
    UK
    Posts
    911
    Thanks
    302
    Thanked 2 Times in 2 Posts
    Quote Originally Posted by Daemonspyre View Post
    Shane --

    You caught me -- I exclusively use MySQL, ergo, I was not familiar with the fact that MS SQL Server didn't use ANSI Standard SQL. (What?! MS not use standards??? No....)

    My example was one of simplicity, and it's not always the best solution.
    Hi

    I do not want to sound like a nerd but what actually is ANSI Standard SQL?


    Thanx

  • #7
    Regular Coder
    Join Date
    Mar 2007
    Posts
    505
    Thanks
    1
    Thanked 19 Times in 19 Posts
    ISO - International Standards Organization
    ANSI - American National Standards Institute

    ISO/ANSI Standards picked up SQL back in the mid-80's. Basically, it says that all RDBMS products should use the same language (SQL) so that they can safely interoperate and ease portability issues.

    Of course, you will find that some RDBMS companies have different functions within their systems, but have the same overall basic features.

    Here's a Wikipedia link for you to peruse at your leisure:

    ISO/ANSI SQL

    NB - You are not a nerd nor a geek. Information is there to be learned. If you aren't learning something new every day, what's the point to life?
    To say my fate is not tied to your fate is like saying, 'Your end of the boat is sinking.' -- Hugh Downs
    Please, if you found my post helpful, pay it forward. Go and help someone else today.

  • #8
    Senior Coder
    Join Date
    Dec 2002
    Location
    Arlington, Texas USA
    Posts
    1,065
    Thanks
    4
    Thanked 8 Times in 8 Posts
    Quote Originally Posted by javabits View Post
    Have you looked at using cursors. I believe they have been available since SQL Server 6.0

    semper fi...
    Cursors are not a good idea as they have a very large overhead involved in using them


  •  

    Posting Permissions

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