offset-clause

The offset-clause specifies the number of rows of the result table to skip before any rows are retrieved.

Syntax for offset-clause

Read syntax diagramSkip visual syntax diagram OFFSET offset-row-count ROWROWS
The offset-clause specifies that the number of rows specified by offset-row-count should be skipped before rows are retrieved. 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
A constant or variable that specifies the number of rows to skip before any rows are retrieved. offset-row-count must be a numeric value that is a positive number or zero. If the value is not a BIGINT value, the value is cast to a BIGINT value. offset-row-count must not be the null value.

When the offset-clause is specified as a prepare attribute, offset-row-count must not reference a variable.

A subselect or fullselect that contains an offset-clause is only allowed as the outermost fullselect in a prepared SQL statement or a DECLARE CURSOR statement. A subselect or fullselect that contains an offset-clause is not allowed in other contexts including the following contexts:
  • The definition of a view
  • The definition of a materialized query table
  • The RETURN statement of an SQL table function
  • The definition of a row permission
  • The definition of a column mask
  • The outermost fullselect for a sensitive dynamic cursor

An offset-clause can also be specified in a SELECT INTO statement.

If a subselect, fullselect, or SELECT INTO statement includes an offset-clause, the select list of that subselect, fullselect, or SELECT INTO statement must not contain an expression that is not deterministic or that has external action.

If the fullselect contains an SQL data change statement in the FROM clause, all rows of the result table are modified regardless of the number of rows to skip.

The offset-clause must not be specified in the outer fullselect for a sensitive dynamic scrollable cursor.

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 these 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.

If both the offset-clause and the ORDER BY clause are specified, the ordering is performed on the entire result table prior to skipping the specified number of rows.

Row access controls can indirectly affect the offset-clause because row access controls affect the rows that are accessible to the authorization ID or role of the fullselect. Column access controls do not affect the offset-clause.

The offset-clause can be used to return rows from a point in the result table to the end of the result table. In this case, the fetch-clause is not specified.

Notes

Syntax alternatives:
The fetch-clause is an alternative for setting the number of rows to skip when specifying the maximum number of rows to retrieve. Seefetch-clause.