Skip to content
Tutorials Center
My learning

Topics

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.

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

Try it

SQL Top rows and a second page ยท database: shop

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

SQL The standard form (FETCH FIRST) in SQLite ยท database: shop

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

Syntax

SQL
-- 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
-- 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 0 returns no rows but still returns the column names, which is handy to inspect a result's shape.
  • Negative values: SQLite treats a negative LIMIT as "no limit"; other databases raise an error.
  • UPDATE and DELETE do not accept LIMIT in standard SQL; use a subquery on primary keys. MySQL and SQLite (when compiled with the option) allow it.
  • For cross-database detail, see LIMIT, TOP and FETCH.