SELECT ... OFFSET ... FETCH
MariaDB starting with 10.6.0
SELECT ... OFFSET ... FETCH
was introduced in MariaDB 10.6.
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.
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 | +------+