Pagination using standard SQL syntax. This clause limits the number of rows returned and skips a specified number of rows, similar to LIMIT.
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 give the same result.
In the case of WITH TIES, an clause is required, otherwise an error will be returned.
Given a table with 6 rows:
OFFSET 2 allows one to skip the first two results:
FETCH FIRST 3 ROWS ONLY limits the results to three rows only:
The same outcome can also be achieved with the clause:
WITH TIES ensures the tied result 4 is also returned:
This page is licensed: CC BY-SA / Gnu FDL
OFFSET start { ROW | ROWS }
FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } { ONLY | WITH TIES }SELECT i FROM t1 FETCH FIRST 2 ROWS WITH TIES;
ERROR 4180 (HY000): FETCH ... WITH TIES requires ORDER BY clause to be presentCREATE 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 |
+------+SELECT i FROM t1 ORDER BY i ASC OFFSET 2 ROWS;
+------+
| i |
+------+
| 3 |
| 4 |
| 4 |
| 5 |
+------+SELECT i FROM t1 ORDER BY i ASC OFFSET 1 ROWS FETCH FIRST 3 ROWS ONLY;
+------+
| i |
+------+
| 2 |
| 3 |
| 4 |
+------+SELECT i FROM t1 ORDER BY i ASC LIMIT 3 OFFSET 1;
+------+
| i |
+------+
| 2 |
| 3 |
| 4 |
+------+SELECT i FROM t1 ORDER BY i ASC OFFSET 1 ROWS FETCH FIRST 3 ROWS WITH TIES;
+------+
| i |
+------+
| 2 |
| 3 |
| 4 |
| 4 |
+------+