LIMIT style functionality in MS SQL Server 2005

I've seen quite a few really horrid ways to do something like MySQL's LIM开发者_JAVA百科IT 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!

---

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

---

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