OFFSET and FETCH FIRST Clauses

Limit the number of rows returned in a result set.

Syntax

OFFSET integer-literal { ROW | ROWS }
FETCH { FIRST | NEXT } [integer-literal] { ROW | ROWS } ONLY

Description

Note: You cannot use the OFFSET or FETCH FIRST clause in a query used to create a view. See CREATE VIEW.

The OFFSET clause provides a way to skip a number of rows in a result set before returning any rows.

The FETCH FIRST clause, which can be combined with the OFFSET clause, limits the number of rows that are returned in the result set. You can use the FETCH FIRST clause to retrieve only a few rows from a large result set. FETCH FIRST is frequently used with an ORDER BY clause.

In both of these clauses, ROW is synonymous with ROWS and FIRST is synonymous with NEXT.

In the OFFSET clause the integer-literal must be equal to 0 (the default if no OFFSET clause is provided), or it must be a positive number. If the OFFSET is larger than the number of rows in the result set, then no rows are returned.

In the FETCH FIRST clause , the integer-literal must be 1 or greater. You can omit the integer-literal entirely to use the default value of 1. With the default value, GemFire XD returns all rows in the result set (or only those rows that are remaining, if the OFFSET clause is used).

Examples

SELECT * FROM T FETCH FIRST ROW ONLY
SELECT * FROM T ORDER BY I OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY
SELECT * FROM T OFFSET 100 ROWS