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!
-
1It'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: stackoverflow.com/questions/603724/…, stackoverflow.com/questions/2135418/…. 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– Andriy M2011-03-01 06:17:29 +00:00Commented Mar 1, 2011 at 6:17
-
Yes, I always take care to have a look through the list - stack overflow fail perhaps? :)John Hunt– John Hunt2011-03-01 22:06:31 +00:00Commented Mar 1, 2011 at 22:06
Add a comment
|
2 Answers
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).
2 Comments
John Hunt
I gave you the tick - your answer is pretty much the same as Sachin Shanbhag's, but you were a bit quicker :p
OMG Ponies
@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.
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
3 Comments
OMG Ponies
That's not the correct equivalent end value based on the offset
John Hunt
Both pretty good answers from Sachin Shanbhag and OMG Ponies, shame Microsoft wouldn't just implement LIMIT...oh well. Thanks!
OMG Ponies
@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).