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 |
+------+

URL: https://mariadb.com/kb/en/select-offset-fetch/