OFFSET FETCH Clause (SQL Server Compact)

SQL Server 2012

The OFFSET-FETCH clause provides you with an option to fetch only a window or page of results from the result set. OFFSET-FETCH can be used only with the ORDER BY clause.

Copy 

[ORDER BY { order_by_expression [ ASC | DESC ] } [ ,...n][<offset_fetch>] ]
Copy 
<offset_fetch> ::= {OFFSET { integer_constant | offset_row_count_expression } { ROW | ROWS }    [FETCH { FIRST | NEXT } {integer_constant | fetch_row_count_expression } { ROW | ROWS } ONLY]}

OFFSET { integer_constant | offset_row_count_expression } { ROW | ROWS }

Specifies the number of rows to skip, before starting to return rows from the query expression. The argument for the OFFSET clause can be an integer or expression that is greater than or equal to zero. You can use ROW and ROWS interchangeably.

FETCH { FIRST|NEXT } <rowcount expression> { ROW|ROWS } ONLY

Specifies the number of rows to return, after processing the OFFSET clause. The argument for the FETCH clause can be an integer or expression that is greater than or equal to one. You can use ROW and ROWS interchangeably. Similarly, FIRST and NEXT can be used interchangeably.

  • ORDER BY is mandatory to use OFFSET and FETCH clause.

  • OFFSET clause is mandatory with FETCH. You can never use, ORDER BY … FETCH.

  • TOP cannot be combined with OFFSET and FETCH in the same query expression.

  • The OFFSET/FETCH rowcount expression can be any arithmetic, constant, or parameter expression that will return an integer value. The rowcount expression does not support scalar sub-queries.

The following examples demonstrate the use of OFFSET-FETCH clause with ORDER BY.

Example 1 Skip first 10 rows from the sorted result set and return the remaining rows.

Copy 
SELECT First Name + ' ' + Last Name FROM Employees ORDER BY First Name OFFSET 10 ROWS;

Example 2- Skip first 10 rows from the sorted resultset and return next 5 rows.

Copy 
SELECT First Name + ' ' + Last Name FROM Employees ORDER BY First Name OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY;
Show: