FETCH FIRST ROWS ONLY Works on PostgreSQL and MariaDB and Fails on MySQL and SQLite

FETCH FIRST 10 ROWS ONLY is the standard way to ask for the top ten, and PostgreSQL and MariaDB accept it. SQLite and MySQL refuse the query.

MariaDB added FETCH FIRST in version 10.6. SQLite and MySQL know only LIMIT, and they refuse OFFSET n ROWS for the same reason.

SELECT x FROM t ORDER BY x FETCH FIRST 2 ROWS ONLY, run on four engines: SQLite 3.45.1 error: near "FETCH": syntax error; PostgreSQL 16.15 1; 2; MySQL 8.0.46 error: You have an error in your SQL syntax ... near 'FETCH FIRST 2 ROWS ONLY' at line 1; MariaDB 10.11.14 1; 2.

What Each Engine Answered

On a table made with

CREATE TABLE t (x INTEGER);
INSERT INTO t VALUES (1), (2), (3);

the example

SELECT x FROM t ORDER BY x FETCH FIRST 2 ROWS ONLY;

gave these answers:

Engine	Answer
SQLite 3.45.1	error: `near "FETCH": syntax error`
PostgreSQL 16.15	`1; 2`
MySQL 8.0.46	error: `You have an error in your SQL syntax ... near 'FETCH FIRST 2 ROWS ONLY' at line 1`
MariaDB 10.11.14	`1; 2`

Every Form BareSQL Checks

BareSQL Portable checks 2 forms of this, and each was run on all four engines. yes means the engine ran it and the engines that ran it agreed, no means the engine refused it, and a value is what the engine answered where they disagreed.

Form	SQLite	PostgreSQL	MySQL	MariaDB	What to write instead
`FETCH FIRST`	no	yes	no	yes
`OFFSET n ROWS`	no	yes	no	yes	Use LIMIT n OFFSET m, which all four accept.

What to Write Instead

This form answers the same on every engine:

SELECT x FROM t ORDER BY x LIMIT 2;

Engine	Answer
SQLite 3.45.1	`1; 2`
PostgreSQL 16.15	`1; 2`
MySQL 8.0.46	`1; 2`
MariaDB 10.11.14	`1; 2`

Use LIMIT n, with OFFSET m when you need an offset.

What BareSQL Portable Reports

Saved as example.sql, the example gets this from baresql lint --all:

$ baresql lint --all example.sql
example.sql:3:26: info: SQLite, MySQL and MariaDB put NULL first in ascending order; PostgreSQL puts it last. [null-order]
    x
    Sort on x IS NULL first: ORDER BY x IS NULL, x puts NULL last on every engine, and ORDER BY x IS NOT NULL, x puts it first. NULLS FIRST and NULLS LAST do not work on MySQL and MariaDB.
example.sql:3:28: error: FETCH FIRST works on PostgreSQL and MariaDB; SQLite and MySQL refuse it. [fetch-first]
    FETCH FIRST 2 ROWS ONLY
    Use LIMIT n, with OFFSET m when you need an offset.

2 problems: 1 error, 1 note.

The example also runs into Where NULL sorts, which has a post of its own.

baresql rules fetch-first prints the same answers in the terminal, and the BareSQL Portable page lists every difference BareSQL knows.