Results 1 to 9 of 9
  1. #1
    Join Date
    May 2005
    Posts
    39

    Unanswered: Is there an MS SQL Limit function?

    MySQL has a convenient syntax for paging data that looks like this:
    Code:
    SELECT * FROM MyTable LIMIT 10, 20
    That would select 10 records, starting from record 20, so that it returns records 20 - 30. This is convenient way to page data, without returning anymore rows than than you need.

    However, MS SQL doesn't appear to support that syntax. What is the equivalent sql code to select any N rows from an arbitrary starting point, without having to create a stored procedure?

    Thanks in advance

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    it's a fiasco

    SQL Server has the TOP keyword, but it takes only one parameter

    see this article -- http://rosca.net/writing/articles/serverside_paging.asp
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    How can is start at row 20 when you have not specified an ORDER BY clause?
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by r123456
    How can is start at row 20 when you have not specified an ORDER BY clause?
    You can't

    Read here

    http://weblogs.sqlteam.com/jeffs/category/162.aspx
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Brett Kaiser
    jeepers, i took a look at one of the two articles posted there, and boy, that sql is inefficient

    brett, did you read the article i posted?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Which one? I thought the server side paging was pretty good....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Andrew's code is very,,,need to compare the 2
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i originally read the second one, and it has issues

    i just now went back and read the first one, and all it is is a dynamic-ization of the second one

    i remain unimpressed

    now, did you read the article i posted?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Yes I did, and it's elegant....but I'd have to test it for performace against some major tables
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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