SQL Server and LIMIT clauses on SELECT statements | The Coffeehouse

Cairo
I want my waffle sundae, give me my carbs!

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

Maurits
AKA Matthew van Eerde

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

Maurits
AKA Matthew van Eerde

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

Maurits
AKA Matthew van Eerde

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

Cairo
I want my waffle sundae, give me my carbs!

Thanks for the suggestions.

I wonder if SQL Server 2005 will support LIMIT.

W3bbo
The Master of Baiters

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

It doesn't Smiley

blowdart
Peek-a-boo

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.

Mike Dimmick

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

Maurits
AKA Matthew van Eerde

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

W3bbo
The Master of Baiters

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?

blowdart
Peek-a-boo

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

Maurits
AKA Matthew van Eerde

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

*snort*

Tensor
Im in yr house upgrading yr family

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

blowdart
Peek-a-boo

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

W3bbo
The Master of Baiters

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.

W3bbo
The Master of Baiters

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.

W3bbo
The Master of Baiters

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.

W3bbo
The Master of Baiters

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.

Tom Servo
W-hat?

Uh, what about

SELECT TOP 5 * FROM table WHERE COUNT(*) > 10

Or something?

Maurits
AKA Matthew van Eerde

Tom Servo wrote:
SELECT TOP 5 * FROM table WHERE COUNT(*) > 10

Heh - too easy. Can't use aggregate functions ( COUNT ) in a WHERE clause. You can use them in a HAVING clause, but then you can't SELECT *...

Cairo
I want my waffle sundae, give me my carbs!

W3bbo wrote:
MySQL-specific SQL extensions, like the "LIMIT" clause.

LIMIT isn't MySQL-specific. In fact, I was unaware that MySQL supported it. I currently use SQL Server, Oracle and SQLite for development. Oracle and SQLite both support LIMIT, and I was looking for something as elegant in SQL Server. I suppose I can insert results into temp tables with an autoincrement field, and select records where ID between X and Y. Or something.

W3bbo
The Master of Baiters

Wait a second...

Can't you just do:

SELECT * FROM table WHERE [ID] < @LowerPageLimit AND [ID] > @UpperPageLimit

?

zzzzz
Yes its an Economy vehicle

The easy way guys

Do up a parameter in the SP this way you can keep looping through the records
I just tested it on my SQL2000 box

Select top 5 * from QcJobMas where job >= MyParameter Order By job

This way the table does not need to be index or anything. it will work on any data type

zzzzz
Yes its an Economy vehicle

time stamps fields work great for this purpose.

Maurits
AKA Matthew van Eerde

I'm beginning to wish SQL Server had LIMIT. If it's SQL99, that should be "standard" enough. And I see a lot of benefits to being able to specify, server-side, that I'm only interested in a particular "page" of data... rather than making a wasteful scrollable cursor.

Two questions...

With LIMIT, how do you know how many pages of data there are? Do you do a COUNT(*) beforehand? Isn't that a pain?

Why doesn't SQL Server 2005 implement LIMIT?