SELECT ... OFFSET ... FETCH

Syntax

OFFSET start { ROW | ROWS }
FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } { ONLY | WITH TIES }

Description

The OFFSET clause allows one to return only those elements of a resultset that come after a specified offset. The FETCH clause specifies the number of rows to return, while ONLY or WITH TIES specifies whether or not to also return any further results that tie for last place according to the ordered resultset.

Either the singular ROW or the plural ROWS can be used after the OFFSET and FETCH clauses; the choice has no impact on the results.

FIRST and NEXT gives the same result.

In the case of WITH TIES, an ORDER BY clause is required, otherwise an ERROR will be returned.

SELECT i FROM t1 FETCH FIRST 2 ROWS WITH TIES;
ERROR 4180 (HY000): FETCH ... WITH TIES requires ORDER BY clause to be present

Examples

Given a table with 6 rows:

CREATE OR REPLACE TABLE t1 (i INT);
INSERT INTO t1 VALUES (1),(2),(3),(4), (4), (5);
SELECT i FROM t1 ORDER BY i ASC;
+------+
| i    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    4 |
|    5 |
+------+

OFFSET 2 allows one to skip the first two results.

SELECT i FROM t1 ORDER BY i ASC OFFSET 2 ROWS;
+------+
| i    |
+------+
|    3 |
|    4 |
|    4 |
|    5 |
+------+

FETCH FIRST 3 ROWS ONLY limits the results to three rows only

SELECT i FROM t1 ORDER BY i ASC OFFSET 1 ROWS FETCH FIRST 3 ROWS ONLY;
+------+
| i    |
+------+
|    2 |
|    3 |
|    4 |
+------+

The same outcome can also be achieved with the LIMIT clause:

SELECT i FROM t1 ORDER BY i ASC LIMIT 3 OFFSET 1;
+------+
| i    |
+------+
|    2 |
|    3 |
|    4 |
+------+

WITH TIES ensures the tied result 4 is also returned.

SELECT i FROM t1 ORDER BY i ASC OFFSET 1 ROWS FETCH FIRST 3 ROWS WITH TIES;
+------+
| i    |
+------+
|    2 |
|    3 |
|    4 |
|    4 |
+------+

See Also

• LIMIT
• ORDER BY
• SELECT