The result offset clause provides a way to skip the N first rows in a result set before starting to return any rows. The fetch first clause, which can be combined with the result offset clause if desired, limits the number of rows returned in the result set. The fetch first clause can sometimes be useful for retrieving only a few rows from an otherwise large result set, usually in combination with an ORDER BY clause. The use of this clause can give efficiency benefits. In addition, it can make programming the application simpler.
OFFSET integer-literal {ROW | ROWS}
FETCH { FIRST | NEXT } [integer-literal] {ROW | ROWS} ONLY
ROW is synonymous with ROWS and FIRST is synonymous with NEXT.
For the result offset clause the integer literal must be equal to 0 (default if the clause is not given), or positive. If it is larger than the number of rows in the underlying result set, no rows are returned.
For the fetch first clause, the literal must be 1 or higher. The literal can be omitted, in which case it defaults to 1. If the clause is omitted entirely, all rows (or those rows remaining if a result offset clause is also given) will be returned.
SELECT * FROM T FETCH FIRST ROW ONLY SELECT * FROM T ORDER BY I OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY SELECT * FROM T OFFSET 100 ROWS