LIMIT function in MS SQL Server 2005

Question

I saw quite a few terrible ways to do something like the MySQL 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?

Hooray!

Answer 1

SQL Server, equivalent to the MySQL / PostgreSQL LIMIT syntax, is TOP (SQL Server 2000+), but TOP does not support the offset value ...

Assuming SQL Server 2005+ uses:

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

Remember that you need to determine the sort order for ranking - replace "?" with the corresponding column (s).

Answer 2

SQL Server LIMIT 5,15 ROW_NUMBER() -

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