Skip to content
Tutorials Center
My learning

Topics

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.

SQL
SELECT name, price FROM products ORDER BY price DESC LIMIT 3;

Try it

SQL Top three, and the next three ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

SQL The cheapest product, with and without ties ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Syntax

SQL
-- 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

SQL
-- 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.
  • LIMIT is applied last, after WHERE, GROUP BY, HAVING, window functions, SELECT and ORDER BY. It cuts the final result, it does not make the earlier steps cheaper unless an index lets the engine stop early.
  • Large OFFSET values 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 1 returns one arbitrary row among ties. Use WITH TIES where 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; use ROW_NUMBER() in a subquery or CTE.
  • Parameters. In prepared statements the limit is usually bound as an integer; some drivers need it cast explicitly.