tsql - How to get N rows starting from row M from sorted table in T-SQL

Question

There is a simple way to get top N rows from any table:

SELECT TOP 10 * FROM MyTable ORDER BY MyColumn

Is there any efficient way to query M rows starting from row N

For example,

Id Value
1    a
2    b
3    c
4    d
5    e
6    f

And query like this

SELECT [3,2] * FROM MyTable ORDER BY MyColumn /* hypothetical syntax */

queries 2 rows starting from 3d row, i.e 3d and 4th rows are returned.

Answer 1

UPDATE If you you are using SQL 2012 new syntax was added to make this really easy. See Implement paging (skip / take) functionality with this query

I guess the most elegant is to use the ROW_NUMBER function (available from MS SQL Server 2005):

WITH NumberedMyTable AS
(
    SELECT
        Id,
        Value,
        ROW_NUMBER() OVER (ORDER BY Id) AS RowNumber
    FROM
        MyTable
)
SELECT
    Id,
    Value
FROM
    NumberedMyTable
WHERE
    RowNumber BETWEEN @From AND @To