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. If offset-row-count specifies more rows than the number of rows in the intermediate result table, the intermediate result table is treated as an empty result table.
- offset-row-count
- An expression that specifies the number of rows to skip before any rows are retrieved. offset-row-count must not contain a scalar-fullselect, a column reference, a table reference, a user-defined function reference, or a built-in scalar function identified as restricted in a check constraint; see check-constraint. If the data type of the expression is not BIGINT, the result of the expression is cast to a BIGINT value. The value of offset-row-count must be a positive number 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.
An offset-clause cannot be specified in the outermost fullselect of a view.
If the fullselect contains an SQL data change statement in the FROM clause, all the rows are modified regardless of the number of rows to skip.
Notes
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-clause for alternative syntax to set the number of rows to skip when specifying the maximum number of rows to retrieve.
