Selecting Specific Row Number In Sql

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

Solution 1:

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

SELECT OrderingColumn 
FROM (
    SELECT OrderingColumn, ROW_NUMBER() OVER (ORDERBY OrderingColumn) AS RowNum
    FROM MyTable
) AS MyDerivedTable
WHERE MyDerivedTable.RowNum BETWEEN@startRowand@endRow

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

Solution 2:

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

SELECT*FROM     MyTable 
ORDERBY OrderingColumn ASCOFFSET0ROWSFETCH NEXT 5ROWSONLY

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