SQL Reference ยท Statements and Clauses
SQL LIMIT and OFFSET
Reference for SQL LIMIT, OFFSET and FETCH FIRST: return the top rows, skip rows for paging, and the TOP and FETCH equivalents in other databases.
LIMIT caps the number of rows a query returns, and OFFSET skips a number of rows first. Together they give you "the top 10" and simple paging: page 3 of 10 rows is LIMIT 10 OFFSET 20.
The standard spelling is OFFSET n ROWS FETCH FIRST m ROWS ONLY. LIMIT is a widely supported shortcut (SQLite, PostgreSQL, MySQL); SQL Server uses TOP or FETCH. Always combine either with ORDER BY, otherwise "the first rows" has no defined meaning.
SELECT name, price FROM products ORDER BY price DESC LIMIT 3;
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
-- SQLite, PostgreSQL, MySQL LIMIT count [ OFFSET skip ] LIMIT skip, count -- MySQL and SQLite only; note the order -- SQL standard (PostgreSQL, Oracle 12c+, SQL Server 2012+, DB2) [ OFFSET skip { ROW | ROWS } ] FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } { ONLY | WITH TIES }
| Part | Description |
|---|---|
count |
Maximum number of rows to return. |
skip |
Number of rows to discard before returning any. Defaults to 0. |
WITH TIES |
Also returns further rows that tie with the last row on the ORDER BY keys (needs ORDER BY). |
Returns: at most count rows, fewer if the result is shorter.
Dialect differences
| Task | SQLite | PostgreSQL | MySQL | SQL Server | Oracle |
|---|---|---|---|---|---|
| First 5 rows | LIMIT 5 |
LIMIT 5 |
LIMIT 5 |
TOP 5 |
FETCH FIRST 5 ROWS ONLY |
| Skip 10, take 5 | LIMIT 5 OFFSET 10 |
same | same | OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY |
same as SQL Server |
WITH TIES |
No | 13+ | No | TOP n WITH TIES |
12c+ |
-- SQL Server SELECT TOP 5 name, price FROM products ORDER BY price DESC; -- Oracle 12c+ and PostgreSQL SELECT name, price FROM products ORDER BY price DESC OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY;
Usage notes
- No
ORDER BY, no meaning. Without a sort, the database may return any rows, and different ones each time. - Make the sort unique. Add a tiebreaker such as
id, or rows can repeat or disappear between pages. - Large offsets are slow. The database still reads and discards the skipped rows. For deep paging use keyset pagination; see pagination strategies.
LIMIT 0returns no rows but still returns the column names, which is handy to inspect a result's shape.- Negative values: SQLite treats a negative
LIMITas "no limit"; other databases raise an error. UPDATEandDELETEdo not acceptLIMITin standard SQL; use a subquery on primary keys. MySQL and SQLite (when compiled with the option) allow it.- For cross-database detail, see
LIMIT,TOPandFETCH.
Related
ORDER BY,SELECT, pagination strategies,ROW_NUMBER()- Lessons: LIMIT and OFFSET, ORDER BY