Export (0) Print
Expand All
Expand Minimize

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.


[ORDER BY { order_by_expression [ ASC | DESC ] } [ ,...n][<offset_fetch>] ] 
<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.

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.

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

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft