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

user_69802 0
Viewed 125429

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.

tsql sql
17 Answers

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
user_157160

Ugly, hackish, but should work:

select top(M + N - 1) * from TableName
except
select top(N - 1) * from TableName
Harper_Shelby_211960 
@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.

user_918140

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)

user_221940

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.

user_380550

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;
JonathanMueller_138320
Related