Coffeehouse Thread

58 posts

Forum Read Only

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

SQL Server and LIMIT clauses on SELECT statements

Back to Forum: Coffeehouse
  • User profile image
    Cairo

    SQL Server 2k does not appear to support the LIMIT clause.

    This should seek to the 10th record in the result and return only five records:

    SELECT fields FROM table LIMIT 10,5;

    How is that accomplished in SQL Server? "TOP" appears to support only the second part of the LIMIT clause, "return only X records".

  • User profile image
    Maurits

    I thought LIMIT was a proprietary vendor extension, not a standard clause.

    You could probably do something like...

    SELECT * FROM (
    SELECT TOP 5 -- actually bottom five of the top fifteen (seek 10)
        *
    FROM
        (
           SELECT TOP 15
              *
           FROM
              Table
           ORDER BY
              Field
        )
    ORDER BY
        Field DESC
    )
    ORDER BY Field -- fix order

  • User profile image
    Maurits

    The way I usually handle this is in the middleware... say, using ADO:

    Set rs = ...
    rs.PageSize = 5
    rs.AbsolutePage = 3 ' items 11-15

    ' if it falls "off the end" then absolutepage changes to a sentinel
    Do Until rs.AbsolutePage <> 3
        ' blah blah
        rs.MoveNext
    Loop

  • User profile image
    Maurits

    The straight subquery syntax above mysteriously fails... a bug?

    Aliasing the subqueries works in SQL Server 2000:

    SELECT * FROM (
    SELECT TOP 5 -- actually bottom five of the top fifteen (seek 10)
        *
    FROM
        (
           SELECT TOP 15
              *
           FROM
              SomeTable
           ORDER BY
              SomeField
        ) AS T -- why is this necessary?
    ORDER BY
        T.SomeField DESC
    ) AS T -- why is this necessary?
    ORDER BY T.SomeField -- fix order

  • User profile image
    Cairo

    Thanks for the suggestions.

    I wonder if SQL Server 2005 will support LIMIT.

  • User profile image
    W3bbo

    Cairo wrote:
    I wonder if SQL Server 2005 will support LIMIT.


    It doesn't Smiley

  • User profile image
    blowdart

    W3bbo wrote:
    Cairo wrote:I wonder if SQL Server 2005 will support LIMIT.


    It doesn't


    Which is sort of fair enough, considering it's not part of the SQL92 standard. Problem is, it is part of SQL99.

  • User profile image
    Mike Dimmick

    SET ROWCOUNT 10 will limit the number of rows SQL Server returns to 10. To turn it off, SET ROWCOUNT 0.

  • User profile image
    Maurits

    Mike Dimmick wrote:
    SET ROWCOUNT 10 will limit the number of rows SQL Server returns to 10. To turn it off, SET ROWCOUNT 0.


    This is effectively a proprietary version of the standard SELECT TOP 10 ... ORDER BY ...

  • User profile image
    W3bbo

    Mike Dimmick wrote:
    SET ROWCOUNT 10 will limit the number of rows SQL Server returns to 10. To turn it off, SET ROWCOUNT 0.


    Ah yes, but how can you use it to implement paging?

  • User profile image
    blowdart

    Beer28 wrote:

    If MySQL didn't have that, it would be so hard to enumerate results as pages. I'm going to look it up in my o'reilly SQL reference book. I don't believe it's proprietary, esp not since MySQL has it.


    It is. Find me a reference to it in SQL92. It started off as an Oracle addition, and has been adopted for SQL99.

    MS missed a huge opportunity with SQL2005 in not implementing SQL99

  • User profile image
    Maurits

    Beer28 wrote:
    I don't believe it's proprietary, esp not since MySQL has it.

    *snort*

  • User profile image
    Tensor

    It doesnt support Limit, but you will be able to achieve the same thing in other ways. Dont have the syntax handy.

  • User profile image
    blowdart

    W3bbo wrote:
    Mike Dimmick wrote:SET ROWCOUNT 10 will limit the number of rows SQL Server returns to 10. To turn it off, SET ROWCOUNT 0.


    Ah yes, but how can you use it to implement paging?


    Well you could

    set rowcount pageNumber * pageSize
    select into #tempKey
      keyField from table order by keyField
    set rowcount pageSize
    select * from table where keyID not in (select keyField from #tempKey) order by keyField

    Or something like that

  • User profile image
    W3bbo

    Beer28 wrote:
    I checked the o'reilly SQL reference, and it only mentioned LIMIT in the MySQL section, so it may be unique to MySQL. Then again, why would anybody want to use anything but MySQL or postgreSQL.


    Because mySQL is terribly underpowered for anything more than your local CS clan's phpNuke CMS.

    And PostgreSQL is ranking halfway between Jet and SQL Server Standard.

    For stuff like hospital records or banking, you really need something beefier, like Oracle, SQL Server, or IBM's DB2.

  • User profile image
    W3bbo

    Beer28 wrote:
    A. What about the $595 MySQL Pro Network server, MaxDB ect...??
    When I outgrow MySQL standard, which I may not, I plan on switching to that and conserving the syntax all of my code. Meanwhile, paying the small fee for an enterprise SQL server.


    Note "Free" and Open Software now, is it? Wink

    Beer28 wrote:
    B. Let's get the facts, where is the proof for your statements. Where does it show that MySQL is less stable or reliable???


    Oh, I dont know.....

    Beer28 wrote:
    MySQL 5 also has stored proceedures, so that's another reason I plan to stick with it.


    ...yes, some 10 years after the rest of the world decided to implement it.

  • User profile image
    W3bbo

    Currently Wikipedia uses MySQL, however it runs on a LOT of servers. I reckon their "TCO" could be reduced if they used something like Oracle or SQL Server, the initial per-processor licensing cost would be circumvented with the cost of all the server hardware and man-hours put into maintaining the system.

  • User profile image
    W3bbo

    Beer28 wrote:
    Secondly, it would lock them into a vendor forever. Like being locked in a POW camp.


    I'd say they're locked into using MySQL anyway. PHP has a lot of built-in functions for MySQL and not other database products, and in all probability, they're also using MySQL-specific SQL extensions, like the "LIMIT" clause.

    Or, they could use a DAL and not be locked into any database or backend solution.

Conversation locked

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