Selecting specific row number in sql [duplicate]

Question

Is any way that I could select specified number of rows in SQL Server? Like on my first query, I wanted to get rows 1-5, then next is rows 6-10, then onwards? Thank you in advance for your answers :)

Answer 1

For SQL Server 2012, try this (simply set the offset)

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

OFFSET:
Specifies the number of rows to skip before it starts to return rows from the query expression.

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

Definitions of OFFSET and FETCH NEXT are from here.

Query 1:
Offset 0 => 1-5

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

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