OFFSET FETCH Clause (SQL Server Compact)
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 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;