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

Ugly, hackish, but should work:

select top(M + N - 1) * from TableName
except
select top(N - 1) * from TableName

Answer 2

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

Answer 3

@start = 3
@records = 2

Select ID, Value 
From
(SELECT ROW_NUMBER() OVER(ORDER BY ID) AS RowNum, ID,Value 
From MyTable) as sub
Where sub.RowNum between @start and @start+@records

This is one way. there are a lot of others if you google SQL Paging.

Answer 4

Find id for row N Then get the top M rows that have an id greater than or equal to that

declare @N as int
set @N = 2
declare @M as int
set @M = 3

declare @Nid as int

set @Nid = max(id)
from
  (select top @N *
from MyTable
order by id)

select top @M *
from MyTable
where id >= @Nid
order by id

Something like that ... but I've made some assumptions here (e.g. you want to order by id)

Answer 5

There is a pretty straight-forward method for T-SQL, although I'm not sure if it is prestanda-effective if you're skipping a large number of rows.

SELECT TOP numberYouWantToTake 
    [yourColumns...] 
FROM yourTable 
WHERE yourIDColumn NOT IN (
    SELECT TOP numberYouWantToSkip 
        yourIDColumn 
    FROM yourTable 
    ORDER BY yourOrderColumn
)
ORDER BY yourOrderColumn

If you're using .Net, you can use the following on for example an IEnumerable with your data results:

IEnumerable<yourDataType> yourSelectedData = yourDataInAnIEnumerable.Skip(nubmerYouWantToSkip).Take(numberYouWantToTake);

This has the backside that you're getting all the data from the data storage.

Answer 6

Why not do two queries:

select top(M+N-1) * from table into temp tmp_final with no log;
select top(N-1) * from tmp_final order by id desc;