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

User · Accepted Answer

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 · Answer

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

User · Answer

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 lt yourDataType gt  yourSelectedData   yourDataInAnIEnumerable Skip nubmerYouWantToSkip  Take numberYouWantToTake     This has the backside that you re getting all the data from the data storage

User · Answer

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

User · Answer

Ugly  hackish  but should work   select top M   N - 1    from TableName except select top N - 1    from TableName

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

In SQL 2012 you can use OFFSET and FETCH   SELECT   FROM MyTable ORDER BY MyColumn OFFSET  N ROWS FETCH NEXT  M ROWS ONLY     I personally prefer   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

User · Answer

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

User · Answer

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

User · Answer

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 · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

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

Examples related to sql

Examples related to tsql