LIMIT / TOP / FETCH FIRSTQuery Techniques
Buy Me A Coffee

Restrict the number of rows returned by a query. Every engine supports this but with different syntax.

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 all LIMIT n or LIMIT offset, n or LIMIT n OFFSET offset. No FETCH FIRST. LIMIT without ORDER BY returns an arbitrary set of rows — always include ORDER BY for deterministic pagination. LIMIT in subqueries has restrictions in older versions.
PostgreSQL ✓ Supported all LIMIT n OFFSET m (non-standard but widely supported). Also supports the SQL-standard FETCH FIRST n ROWS ONLY since PostgreSQL 8.4. LIMIT ALL means no limit. OFFSET without LIMIT is valid.
SQL Server ✓ Supported 2012 OFFSET ... FETCH is the standard syntax (SQL Server 2012+). TOP n is available in all versions but is non-standard and does not support OFFSET. TOP with PERCENT and WITH TIES options available. OFFSET FETCH requires ORDER BY.
Oracle ✓ Supported 12c FETCH FIRST is the SQL-standard syntax, supported since 12c. Before 12c, use ROWNUM in a subquery (notoriously tricky — ROWNUM is assigned before ORDER BY). OFFSET ... FETCH supported in 12c+.
SQLite ✓ Supported all LIMIT n OFFSET m. Also accepts LIMIT offset, n (comma syntax, same as MySQL). LIMIT -1 means no limit (useful for OFFSET without a row cap). No FETCH FIRST.

Details

The SQL-standard syntax is OFFSET m ROWS FETCH NEXT n ROWS ONLY — supported by PostgreSQL, SQL Server 2012+, and Oracle 12c+. MySQL and SQLite use the non-standard but simpler LIMIT/OFFSET. Oracle's pre-12c ROWNUM approach is famously error-prone (ROWNUM is applied before ORDER BY, requiring a subquery wrapper). Always include ORDER BY — without it, which rows you get is undefined.

Standard Syntax

-- Standard SQL (supported by most modern engines): SELECT * FROM tbl ORDER BY col OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY; -- Common alternatives: SELECT * FROM tbl LIMIT 10 OFFSET 20; -- MySQL, PostgreSQL, SQLite SELECT TOP 10 * FROM tbl; -- SQL Server

Version Support

MySQL: Since all PostgreSQL: Since all SQL Server: Since 2012 Oracle: Since 12c SQLite: Since all

Per-Database Syntax & Notes

MySQL

LIMIT n or LIMIT offset, n or LIMIT n OFFSET offset. No FETCH FIRST. LIMIT without ORDER BY returns an arbitrary set of rows — always include ORDER BY for deterministic pagination. LIMIT in subqueries has restrictions in older versions.

SELECT * FROM tbl ORDER BY id LIMIT 10; -- first 10 SELECT * FROM tbl ORDER BY id LIMIT 10 OFFSET 20; -- rows 21-30 SELECT * FROM tbl ORDER BY id LIMIT 20, 10; -- offset,count shorthand

PostgreSQL

LIMIT n OFFSET m (non-standard but widely supported). Also supports the SQL-standard FETCH FIRST n ROWS ONLY since PostgreSQL 8.4. LIMIT ALL means no limit. OFFSET without LIMIT is valid.

SELECT * FROM tbl ORDER BY id LIMIT 10; SELECT * FROM tbl ORDER BY id LIMIT 10 OFFSET 20; -- Standard SQL: SELECT * FROM tbl ORDER BY id OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY; -- No limit: SELECT * FROM tbl ORDER BY id LIMIT ALL OFFSET 5;

SQL Server

OFFSET ... FETCH is the standard syntax (SQL Server 2012+). TOP n is available in all versions but is non-standard and does not support OFFSET. TOP with PERCENT and WITH TIES options available. OFFSET FETCH requires ORDER BY.

-- Modern standard: SELECT * FROM tbl ORDER BY id OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY; -- Legacy TOP (all versions): SELECT TOP 10 * FROM tbl ORDER BY id; SELECT TOP 10 PERCENT * FROM tbl ORDER BY id; SELECT TOP 10 WITH TIES * FROM tbl ORDER BY id;

Oracle

FETCH FIRST is the SQL-standard syntax, supported since 12c. Before 12c, use ROWNUM in a subquery (notoriously tricky — ROWNUM is assigned before ORDER BY). OFFSET ... FETCH supported in 12c+.

-- Modern (12c+): SELECT * FROM tbl ORDER BY id FETCH FIRST 10 ROWS ONLY; SELECT * FROM tbl ORDER BY id OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY; -- Legacy ROWNUM (pre-12c — must wrap in subquery): SELECT * FROM ( SELECT * FROM tbl ORDER BY id ) WHERE ROWNUM <= 10;

SQLite

LIMIT n OFFSET m. Also accepts LIMIT offset, n (comma syntax, same as MySQL). LIMIT -1 means no limit (useful for OFFSET without a row cap). No FETCH FIRST.

SELECT * FROM tbl ORDER BY id LIMIT 10; SELECT * FROM tbl ORDER BY id LIMIT 10 OFFSET 20; -- Offset without row cap: SELECT * FROM tbl ORDER BY id LIMIT -1 OFFSET 20;