SQL Reference ยท Dialects and Concepts
SQL LIMIT, TOP and FETCH across dialects
How to return only the first N rows in SQLite, PostgreSQL, MySQL, SQL Server and Oracle: LIMIT, OFFSET, TOP, FETCH FIRST, ties and stable ordering.
Every database can return just the first few rows of a result, but the keyword differs. SQLite, PostgreSQL and MySQL use LIMIT. SQL Server uses TOP, and also supports the standard OFFSET ... FETCH. Oracle 12c and later use the standard FETCH FIRST. PostgreSQL, Db2 and recent versions of the others understand the standard form as well.
The row limit is only meaningful with an ORDER BY. Without one, the database may return any rows it likes, and different ones on different runs.
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 SELECT ... ORDER BY ... LIMIT row_count [OFFSET skipped]; SELECT ... ORDER BY ... LIMIT skipped, row_count; -- MySQL and SQLite shorthand -- Standard SQL: PostgreSQL, Oracle 12c+, SQL Server 2012+, Db2 SELECT ... ORDER BY ... OFFSET skipped ROWS FETCH { FIRST | NEXT } row_count { ROW | ROWS } { ONLY | WITH TIES }; -- SQL Server SELECT TOP (row_count) [PERCENT] [WITH TIES] ... ORDER BY ...;
| Part | Description |
|---|---|
row_count |
How many rows to return at most. |
OFFSET |
How many rows to skip first. |
WITH TIES |
Also return rows that tie with the last returned row on the ORDER BY columns. Needs an ORDER BY. |
PERCENT |
Return a percentage of the rows (TOP ... PERCENT; FETCH FIRST n PERCENT ROWS in Oracle). |
Note the order in the LIMIT a, b shorthand: the first number is the offset, the second the count. It is easy to swap them by mistake, so many teams only use LIMIT n OFFSET m.
Dialect differences
-- PostgreSQL, SQLite, MySQL SELECT name FROM products ORDER BY price DESC LIMIT 5 OFFSET 10; -- SQL Server (classic) SELECT TOP (5) name FROM products ORDER BY price DESC; -- SQL Server 2012+ (paging; ORDER BY is required) SELECT name FROM products ORDER BY price DESC OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY; -- Oracle 12c+ and PostgreSQL SELECT name FROM products ORDER BY price DESC OFFSET 10 ROWS FETCH FIRST 5 ROWS ONLY; -- Oracle before 12c SELECT name FROM ( SELECT name FROM products ORDER BY price DESC ) WHERE ROWNUM <= 5;
| Feature | SQLite | PostgreSQL | MySQL | SQL Server | Oracle |
|---|---|---|---|---|---|
LIMIT |
yes | yes | yes | no | no |
TOP |
no | no | no | yes | no |
FETCH FIRST |
no | yes | no | yes (with OFFSET) |
yes |
WITH TIES |
no | 13+ | no | yes | yes |
ROWNUM |
no | no | no | no | yes (old style) |
Notes on the table: LIMIT accepts an expression in SQLite (as in the last example above) but only a constant or parameter in MySQL and PostgreSQL. In SQL Server, TOP works without ORDER BY (the rows are then arbitrary), while OFFSET ... FETCH requires it. MySQL does not support FETCH FIRST.
Usage notes
- Always add an
ORDER BY, and make it deterministic. Adding the primary key as a last sort column (ORDER BY price DESC, id) keeps pages stable when prices tie. LIMITis applied last, afterWHERE,GROUP BY,HAVING, window functions,SELECTandORDER BY. It cuts the final result, it does not make the earlier steps cheaper unless an index lets the engine stop early.- Large
OFFSETvalues are slow. The database still walks past all skipped rows. For deep pagination, remember the last seen key and filter (WHERE id > :last_id ORDER BY id LIMIT 20), known as keyset pagination. - Ties.
LIMIT 1returns one arbitrary row among ties. UseWITH TIESwhere available, or compare against a subquery as in the example above. - Per-group top rows (best product per category) are not a job for
LIMIT; useROW_NUMBER()in a subquery or CTE. - Parameters. In prepared statements the limit is usually bound as an integer; some drivers need it cast explicitly.