SQL Server and LIMIT clauses on SELECT statements

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".

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

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

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

Thanks for the suggestions.

I wonder if SQL Server 2005 will support LIMIT.

Cairo wrote:

I wonder if SQL Server 2005 will support LIMIT.

It doesn't

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

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 ...

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?

Beer28 wrote:

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

*snort*

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

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.

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?

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.

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.

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.