Choosing a specific row number in sql

Question

Is there any way to select the specified number of rows in SQL Server? As in my first query, I wanted to get lines 1-5, then the next lines 6-10, then and on? Thank you in advance for your answers :)

Answer 1

For SQL Server 2005+ (install @startRow and @endRow):

SELECT OrderingColumn FROM ( SELECT OrderingColumn, ROW_NUMBER() OVER (ORDER BY OrderingColumn) AS RowNum FROM MyTable ) AS MyDerivedTable WHERE MyDerivedTable.RowNum BETWEEN @startRow and @endRow 

SQL script example: http://sqlfiddle.com/#!3/b4b8c/4

Answer 2

For SQL Server 2012 try this (just set the offset)

 SELECT * FROM MyTable ORDER BY OrderingColumn ASC OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY 

OFFSET :
Indicates the number of skipped rows before it starts returning rows from the query expression.

FETCH NEXT :
Specifies the number of rows returned after the OFFSET clause has been processed.

The definitions of OFFSET and FETCH NEXT are taken from here .

Request 1:
Offset 0 => 1-5

Request 2:
Offset 5 => 6-10, etc.

SQL script example: http://sqlfiddle.com/#!6/b4b8c/2