offset-clause

The offset-clause sets the number of rows to skip.

Read syntax diagramSkip visual syntax diagram OFFSET offset-row-count ROWROWS

The offset-clause specifies the number of rows to skip before any rows are retrieved. Use this clause to communicate to the database manager that the application does not start retrieving rows until offset-row-count rows are 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.

Determining a predictable set of rows to skip requires the specification of an ORDER BY clause with sort keys that would 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.

offset-row-count
An expression that specifies the number of rows to skip before any rows are retrieved. The expression must not contain a column reference, a scalar-fullselect, a function that is not deterministic, a function that has an external action, or a sequence reference (SQLSTATE 428H7). The numeric value must be a positive number or zero (SQLSTATE 2201X). If the data type of the expression is not BIGINT, the result of the expression is cast to a BIGINT value.

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

  • The keywords ROW and ROWS can be used interchangeably. The result is unchanged; however, using ROWS is more readable when associated with a number of rows other than 1.
  • Syntax alternatives: See the Notes entry that is associated with the fetch-clause for alternative syntax to set the number of rows to skip when the maximum number of rows to retrieve is specified.