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!

Answer 1

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