Fetch First n and Offset n
The OFFSET clause and FETCH FIRST clause on the SELECT statement can be used to return a subset of rows from a result set.
Users can code the “n” value of a FETCH FIRST n or OFFSET n specification in a SELECT statement as a host language variable in embedded SQL applications, or as a parameter or local variable in a database procedure.
This feature is useful in Web-style applications that page results back to the user, as in the results from using a web search engine.
For example, the following query returns rows starting from the 25th row of the result set:
SELECT * FROM MYTABLE ORDER BY COL1 OFFSET 25
For example, the following query fetches only the first 10 rows of the result set:
SELECT * FROM MYTABLE ORDER BY COL1 FETCH FIRST 10 ROWS ONLY
A query can use any combination of the ORDER BY, OFFSET, and FETCH FIRST clauses, but in that order only.
The OFFSET and FETCH FIRST clauses can be used only once per query, and cannot be used in unions or view definitions. They cannot be used in subselects, except a subselect in a CREATE TABLE statement or an INSERT statement.
The FETCH FIRST clause cannot be used in the same SELECT statement as SELECT FIRST rowcount.
The syntax for each clause is OFFSET n or FETCH FIRST n, where n is a positive integer, a host variable, or a procedure parameter or local variable.
In the FETCH FIRST clause, the keywords FIRST and NEXT, and the keywords ROWS and ROW are interchangeable. Because you can offset and fetch first in the same query, NEXT is an alternative for readability. For example:
OFFSET 10 FETCH NEXT 25 ROWS ONLY