FETCH
SQL · Limiting Rows
Theory
OFFSET ... FETCH is the SQL-standard way to limit rows. OFFSET m ROWS skips the first m rows of the sorted result and FETCH NEXT n ROWS ONLY returns the following n. FETCH FIRST and FETCH NEXT mean the same thing, as do ROW and ROWS. Page 3 with 10 rows per page is OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY.
It is supported by PostgreSQL, Oracle, Db2 and SQL Server (where it needs an ORDER BY). MySQL and SQLite use LIMIT n OFFSET m for the same job. Some engines also support FETCH FIRST n ROWS WITH TIES, which keeps extra rows that tie with the last one on the ORDER BY value. Because the engine still has to walk past the skipped rows, very large offsets get slow.
Why we use it
Applications show long lists one page at a time. OFFSET/FETCH expresses "skip these, give me the next page" in portable, standard syntax that works across several major engines.
When to use it
Use OFFSET/FETCH for pagination and top-N queries when you want standard SQL or work on Oracle or SQL Server. On MySQL or SQLite use LIMIT/OFFSET. For very deep pages prefer keyset paging (WHERE id > last_seen_id).
How it works
- Sort the result with ORDER BY
- Skip the first m rows (OFFSET m ROWS)
- Return the next n rows (FETCH NEXT n ROWS ONLY)
- WITH TIES requested? Yes: Also keep rows tied with the last one. No: Stop exactly at n rows.
- Return the page
Syntax
SELECT columns FROM table
OFFSET m ROWS FETCH NEXT n ROWS ONLY;Example
SELECT * FROM employees
OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY;Key points
- Standard SQL syntax supported by SQL Server, Oracle, PostgreSQL and Db2
- Common for building pagination
- FIRST/NEXT and ROW/ROWS are interchangeable words
- SQL Server requires ORDER BY with OFFSET/FETCH
- MySQL and SQLite use LIMIT n OFFSET m instead