FETCH FIRST / OFFSET-FETCHQuery Techniques
Buy Me A Coffee

Standard SQL syntax for skipping a number of rows and limiting the result set size, used for pagination.

Restricts the number of rows returned: LIMIT n OFFSET m (MySQL/PostgreSQL), TOP n (SQL Server), FETCH FIRST n ROWS ONLY (standard SQL). For pagination, OFFSET becomes expensive for large skip values -- keyset pagination scales better.

Compatibility

Show:
Database System Support Status Since Version Notes
MySQL ✓ Supported 8.0 OFFSET/FETCH FIRST syntax added in 8.0 as an alias for LIMIT/OFFSET. The original LIMIT n OFFSET m syntax remains available and is more common in practice.
PostgreSQL ✓ Supported 8.4 Full OFFSET/FETCH FIRST support since 8.4. LIMIT/OFFSET also remains available. FETCH FIRST n ROWS WITH TIES requires PostgreSQL 13+.
SQL Server ✓ Supported 2012 OFFSET/FETCH ONLY is the standard SQL Server pagination syntax since 2012. Requires an ORDER BY clause — omitting it is a syntax error. TOP N is the older alternative but does not support offset.
Oracle ✓ Supported 12c OFFSET/FETCH added in Oracle 12c. Prior versions required the ROWNUM pseudo-column or ROW_NUMBER() window function wrapped in a subquery.
SQLite ✓ Supported 3.25.0 OFFSET/FETCH FIRST syntax added in 3.25.0. The traditional LIMIT/OFFSET form has been available since very early SQLite versions.

Details

OFFSET/FETCH is the ISO SQL standard pagination syntax and is now supported across all five engines, though it arrived late in MySQL (8.0), Oracle (12c), and SQLite (3.25.0). For broad compatibility, the LIMIT/OFFSET form works in MySQL, PostgreSQL, and SQLite but not in SQL Server or Oracle. SQL Server's TOP N predates OFFSET/FETCH but cannot paginate (no offset). Oracle's pre-12c ROWNUM pattern is notorious for requiring double-nested subqueries and is a common source of bugs. A key performance note: OFFSET-based pagination degrades at large offsets because the engine must scan and discard rows — keyset pagination (WHERE id > last_seen_id) is more efficient for deep pages.

Standard Syntax

SELECT * FROM t ORDER BY id OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

Version Support

MySQL: Since 8.0 PostgreSQL: Since 8.4 SQL Server: Since 2012 Oracle: Since 12c SQLite: Since 3.25.0

Per-Database Syntax & Notes

MySQL

OFFSET/FETCH FIRST syntax added in 8.0 as an alias for LIMIT/OFFSET. The original LIMIT n OFFSET m syntax remains available and is more common in practice.

-- Standard (8.0+): SELECT * FROM t ORDER BY id OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY; -- Traditional: SELECT * FROM t ORDER BY id LIMIT 10 OFFSET 20;

PostgreSQL

Full OFFSET/FETCH FIRST support since 8.4. LIMIT/OFFSET also remains available. FETCH FIRST n ROWS WITH TIES requires PostgreSQL 13+.

SELECT * FROM t ORDER BY id OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

SQL Server

OFFSET/FETCH ONLY is the standard SQL Server pagination syntax since 2012. Requires an ORDER BY clause — omitting it is a syntax error. TOP N is the older alternative but does not support offset.

SELECT * FROM t ORDER BY id OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

Oracle

OFFSET/FETCH added in Oracle 12c. Prior versions required the ROWNUM pseudo-column or ROW_NUMBER() window function wrapped in a subquery.

-- 12c+: SELECT * FROM t ORDER BY id OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY; -- Pre-12c: SELECT * FROM (SELECT t.*, ROWNUM rn FROM (SELECT * FROM t ORDER BY id) t WHERE ROWNUM <= 30) WHERE rn > 20;

SQLite

OFFSET/FETCH FIRST syntax added in 3.25.0. The traditional LIMIT/OFFSET form has been available since very early SQLite versions.

SELECT * FROM t ORDER BY id OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;