OFFSET clause

Omits a specified number of rows from the beginning of the result set.

Omits a specified number of rows from the beginning of the result set.

Syntax

OFFSET rows

Parameters

start-row
Specifies the first row to include in the result set. All preceding rows are omitted.

Dependencies

  • Use an ORDER BY clause with OFFSET. Otherwise, the query returns an undefined subset of the result set.

  • OFFSET must follow the ORDER BY clause in a SELECT statement or UNION clause.

  • When a SELECT statement or UNION clause specifies both LIMIT and OFFSET, OpenText™ Analytics Database first processes the OFFSET statement, and then applies the LIMIT statement to the remaining rows.

Examples

The following query returns 14 rows from the customer_dimension table:

=> SELECT customer_name, customer_gender FROM customer_dimension
   WHERE occupation='Dancer' AND customer_city = 'San Francisco' ORDER BY customer_name;
    customer_name     | customer_gender
----------------------+-----------------
 Amy X. Lang          | Female
 Anna H. Li           | Female
 Brian O. Weaver      | Male
 Craig O. Pavlov      | Male
 Doug Z. Goldberg     | Male
 Harold S. Jones      | Male
 Jack E. Perkins      | Male
 Joseph W. Overstreet | Male
 Kevin . Campbell     | Male
 Raja Y. Wilson       | Male
 Samantha O. Brown    | Female
 Steve H. Gauthier    | Male
 William . Nielson    | Male
 William Z. Roy       | Male
(14 rows)

If you modify the previous query to specify an offset of 8 (OFFSET 8), the database skips the first eight rows of the previous result set. The query returns the following results:

=> SELECT customer_name, customer_gender FROM customer_dimension
   WHERE occupation='Dancer' AND customer_city = 'San Francisco' ORDER BY customer_name OFFSET 8;
   customer_name   | customer_gender
-------------------+-----------------
 Kevin . Campbell  | Male
 Raja Y. Wilson    | Male
 Samantha O. Brown | Female
 Steve H. Gauthier | Male
 William . Nielson | Male
 William Z. Roy    | Male
(6 rows)