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

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.

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)

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

Probably good for small results, works in all versions of TSQL:

SELECT 
        * 
FROM
     (SELECT TOP (N) * 
      FROM 
            (SELECT TOP (M + N - 1) 
             FROM 
                   Table
             ORDER BY 
                      MyColumn) qasc
      ORDER BY 
               MyColumn DESC) qdesc
 ORDER BY 
         MyColumn

In SQL 2012 you can use OFFSET and FETCH:

SELECT *
FROM MyTable
ORDER BY MyColumn
OFFSET @N ROWS
FETCH NEXT @M ROWS ONLY;

DECLARE @CurrentSetNumber int = 0;
DECLARE @NumRowsInSet int = 2;

SELECT *
FROM MyTable
ORDER BY MyColumn
OFFSET @NumRowsInSet * @CurrentSetNumber ROWS
FETCH NEXT @NumRowsInSet ROWS ONLY;

SET @CurrentSetNumber = @CurrentSetNumber + 1;

where @NumRowsInSet is the number of rows you want returned and @CurrentSetNumber is the number of @NumRowsInSet to skip.

In order to do this in SQL Server, you must order the query by a column, so you can specify the rows you want.

You can't use the "TOP" keyword when doing this, you must use offset N rows fetch next M rows.

Example:

select * from table order by [some_column] 
offset 10 rows
FETCH NEXT 10 rows only

You can learn more here: https://technet.microsoft.com/pt-br/library/gg699618%28v=sql.110%29.aspx

Ugly, hackish, but should work:

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

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;

SELECT * FROM (
  SELECT
    Row_Number() Over (Order by (Select 1)) as RawKey,
    * 
  FROM [Alzh].[dbo].[DM_THD_TRANS_FY14]
) AS foo
WHERE RawKey between 17210400 and 17210500

I read all of the responses here and finally came up with a usable solution that is simple. The performance issues arise from the BETWEEN statement, not the generation of the row numbers themselves. So I used an algorithm to do dynamic paging by passing the page number and the number of records.

The passes are not start row and number of rows, but rather "rows per page (500)" and "page number (4)" which would be rows 1501 - 2000. These values can be replaced by stored procedure variables so you are not locked into using a specific paging amount.

select * from (
    select
        (((ROW_NUMBER() OVER(ORDER BY MyField) - 1) / 500) + 1) AS PageNum
        , *
    from MyTable
) as PagedTable
where PageNum = 4;

        -- *some* implementations may support this syntax (mysql?)
SELECT Id,Value
FROM xxx
ORDER BY Id
LIMIT 2 , 0
   ;

        -- Separate LIMIT, OFFSET
SELECT Id,Value
FROM xxx
ORDER BY Id
LIMIT 2 OFFSET 2
   ;

        -- SQL-2008 syntax
SELECT Id,Value
FROM xxx
ORDER BY Id
OFFSET 4
FETCH NEXT 2 ROWS ONLY
  ;

If you want to select 100 records from 25th record:

select TOP 100 * from TableName
where PrimaryKeyField 
   NOT IN(Select TOP 24 PrimaryKeyField from TableName);

The problem with the suggestions in this thread and elsewhere on the web is that all the proposed solutions run in linear time with respect to the number of records. For example, consider a query like the following.

select *
from
(
    select
        Row_Number() over (order by ClusteredIndexField) as RowNumber,
        *
    from MyTable
) as PagedTable
where RowNumber between @LowestRowNumber and @HighestRowNumber;

When getting page 1, the query takes 0.577 seconds. However, when getting page 15,619, this same query takes over 2 minutes and 55 seconds.

We can greatly improve this by creating a record number, index cross-table as shown in the following query. The cross-table is called PagedTable and is non-persistent.

select *
from
(
    select
        Row_Number() over (order by Field1 asc, Field2 asc, Field3 asc) as RowNumber,
        ClusteredIndexField
    from MyTable
) as PagedTable
left join MyTable on MyTable.ClusteredIndexField = PagedTable.ClusteredIndexField
where RowNumber between @LowestRowNumber and @HighestRowNumber;

Like in the previous example, I tested this on a very wide table with 780,928 records. I used a page size of 50, which resulted in 15,619 pages.

The total time taken for page 1 (the first page) is 0.413 seconds. The total time taken for page 15,619 (the last page) is 0.987 seconds, merely twice times as long as page 1. These times were measured using SQL Server Profiler and the DBMS was SQL Server 2008 R2.

This solution works for any case when you are sorting your table by an index. The index does not have to be clustered or simple. In my case, the index was composed of three fields: varchar(50) asc, varchar(15) asc, numeric(19,0) asc. That the performance was excellent despite the cumbersome index just further demonstrates that this approach works.

However, it is critical that the order by clause in the Row_Number windowing function corresponds to an index. Otherwise performance will degrade to the same level as the first example.

This approach does still require a linear operation to generate the non-persistent cross-table, but since that's just an index with a row number added, it happens very quickly. In my case it took 0.347 seconds, but my case had varchars that needed to be copied. A single numeric index would take far less time.

For all practical purposes, this design reduces the scaling of server-side paging from a linear operation to a logarithmic operation allowing the scaling of large tables. Below is the complete solution.

-- For a sproc, make these your input parameters
declare
    @PageSize int = 50,
    @Page int = 15619;

-- For a sproc, make these your output parameters
declare @RecordCount int = (select count(*) from MyTable);
declare @PageCount int = ceiling(convert(float, @RecordCount) / @PageSize);
declare @Offset int = (@Page - 1) * @PageSize;
declare @LowestRowNumber int = @Offset;
declare @HighestRowNumber int = @Offset + @PageSize - 1;

select
    @RecordCount as RecordCount,
    @PageCount as PageCount,
    @Offset as Offset,
    @LowestRowNumber as LowestRowNumber,
    @HighestRowNumber as HighestRowNumber;

select *
from
(
    select
        Row_Number() over (order by Field1 asc, Field2 asc, Field3 asc) as RowNumber,
        ClusteredIndexField
    from MyTable
) as PagedTable
left join MyTable on MyTable.ClusteredIndexField = PagedTable.ClusteredIndexField
where RowNumber between @LowestRowNumber and @HighestRowNumber;

This thread is quite old, but currently you can do this: much cleaner imho

SELECT *
FROM Sales.SalesOrderDetail
ORDER BY SalesOrderDetailID
OFFSET 20 ROWS
FETCH NEXT 10 ROWS ONLY;
GO

source: http://blog.sqlauthority.com/2013/12/30/sql-server-mysql-limit-and-offset-skip-and-return-only-next-few-rows-paging-solution/

And this is how you can achieve same goal on tables without primary key:

select * from
(
    select row_number() over(order by (select 0)) rowNum,*
    from your_table
) tmp
where tmp.rowNum between 20 and 30 -- any numbers you need

Following is the simple query will list N rows from M+1th row of the table. Replace M and N with your preferred numbers.

Select Top N B.PrimaryKeyColumn from 
    (SELECT 
        top M PrimaryKeyColumn
     FROM 
        MyTable
) A right outer join MyTable B 
on 
    A.PrimaryKeyColumn = B.PrimaryKeyColumn
where 
    A.PrimaryKeyColumn IS NULL

Please let me know whether this is usefull for your situation.

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.