offset-clause
The offset-clause sets the number of rows to skip before any rows are retrieved. It lets the database manager know that the application does not want to start retrieving rows until offset-row-count rows have been skipped. If offset-clause is not specified, the default is equivalent to OFFSET 0 ROWS. An attempt to skip more rows than the number of rows in the intermediate result table is handled the same way as an empty result table.
- offset-row-count
- A numeric constant or numeric variable (except not a global variable) that specifies the number of rows to skip before any rows are retrieved. The numeric value is cast to BIGINT before processing. The value of offset-row-count must be positive or zero. It cannot be the null value.
Determining a predictable set of rows to skip requires the specification of an ORDER BY clause with sort keys that uniquely identify the sort order of each row in the intermediate result table. If the intermediate result table includes duplicate sort keys for some rows, the order of the rows is not deterministic. If there is no ORDER BY clause, the intermediate result table is not in a deterministic order. If the order of the intermediate result table is not deterministic, the set of skipped rows is unpredictable.
Notes
Allowed use of OFFSET: The offset-clause is only allowed as part of the outer fullselect of a DECLARE CURSOR statement or a prepared select-statement. It is not supported in interactive SQL.
select-list restriction: The select-list of the fullselect containing an offset-clause must not contain a NEXT VALUE sequence expression and must not contain a function that is non-deterministic, external action, or modifies SQL data.
Syntax alternatives: See the Notes entry associated with the fetch-first-clause for alternative syntax to set the number of rows to skip when specifying the maximum number of rows to retrieve.
