LIMIT style functionality in MS SQL Server 2005

Question

I've seen quite a few really horrid ways to do something like MySQL's LIMIT function for MS SQL.

Can anyone suggest a nice elegant way to do something like this:

SELECT * FROM blah LIMIT 5,15;

but in MS SQL?

Cheers!

It's interesting how the search for related questions works in different situations on SO. Currently the *Related* list contains the links to some questions that might very well eliminate the need for this one, namely these: http://stackoverflow.com/questions/603724/how-to-implement-limit-with-microsoft-sql-server, http://stackoverflow.com/questions/2135418/equivalent-of-limit-and-offset-for-sql-server. Yet when I clicked *Ask Question* and entered this question's title, the list of related questions wasn't the same, and it certainly didn't have anything as relevant as the questions mentioned. — Andriy M, Mar 01 '11 at 06:17
Yes, I always take care to have a look through the list - stack overflow fail perhaps? :) — John Hunt, Mar 01 '11 at 22:06

score 4 · Accepted Answer · answered Mar 01 '11 at 06:01

4

SQL Server's equivalent to MySQL/PostgreSQL's LIMIT syntax is TOP (SQL Server 2000+), but TOP doesn't support the offset value...

Assuming SQL Server 2005+, use:

SELECT x.*
  FROM (SELECT t.*,
               ROW_NUMBER() OVER (ORDER BY ?) AS rank
          FROM BLAH t) x
 WHERE x.rank BETWEEN 6 AND 20

Mind that you have to define a sort order for the ranking - replace the "?" with the appropriate column(s).

answered Mar 01 '11 at 06:01

OMG Ponies

I gave you the tick - your answer is pretty much the same as Sachin Shanbhag's, but you were a bit quicker :p – John Hunt Mar 01 '11 at 06:11
@John Hunt: Actually, Sachin was first but I stand by my comment that the Sachin's limit implementation isn't exactly what MySQL's LIMIT would produce given equivalent data. – OMG Ponies Mar 01 '11 at 06:13

Sachin Shanbhag · Answer 2 · 2011-03-01T06:09:44.437

2

One of the ways to obtain the same in SQL Server for LIMIT 5,15 would be to use ROW_NUMBER() -

With t As
(
    Select ...
        , ROW_NUMBER() OVER ( Order By ... ) As Num
    From Table
)
Select ...
From t
Where Num Between 5 And 15

edited Mar 01 '11 at 06:09

answered Mar 01 '11 at 06:01

Sachin Shanbhag

That's not the correct equivalent [end value based on the offset](http://dev.mysql.com/doc/refman/5.5/en/select.html) – OMG Ponies Mar 01 '11 at 06:04
2

Both pretty good answers from Sachin Shanbhag and OMG Ponies, shame Microsoft wouldn't just implement LIMIT...oh well. Thanks! – John Hunt Mar 01 '11 at 06:10
@John Hunt: Luckily syntax has been ANSI'd, but it's `FETCH FIRST x ROWS` -- DB2 is the only one that currently supports it (to my knowledge). – OMG Ponies Mar 01 '11 at 06:12

2 Answers2