SQL LIMIT and OFFSET: Return the Top N Rows (and TOP, FETCH FIRST)

Sometimes you only need a few rows: the five most expensive products, the latest order, the first page of search results. LIMIT stops the result after a given number of rows.

The example table#

Syntax#

SELECT columns
FROM table_name
ORDER BY column
LIMIT number;

LIMIT is the last clause in the query.

Top N rows#

The three highest-paid employees:

SELECT name, salary
FROM employees
ORDER BY salary DESC
LIMIT 3;

name  | salary
------+-------
Meera | 81000
Ravi  | 78000
Priya | 61000

The query first sorts everyone by salary, then keeps the first three rows. LIMIT without ORDER BY returns an arbitrary set of rows, so "top N" questions always need both.

The longest-serving employee:

SELECT name, hire_date
FROM employees
ORDER BY hire_date
LIMIT 1;

name  | hire_date
------+-----------
Meera | 2018-01-10

Skipping rows with OFFSET#

OFFSET skips a number of rows before LIMIT starts counting. This is how websites show results page by page:

-- Page 1: rows 1-2
SELECT name, salary
FROM employees
ORDER BY salary DESC
LIMIT 2;

name  | salary
------+-------
Meera | 81000
Ravi  | 78000

-- Page 2: skip 2 rows, then take 2
SELECT name, salary
FROM employees
ORDER BY salary DESC
LIMIT 2 OFFSET 2;

name  | salary
------+-------
Priya | 61000
Asha  | 52000

For page p with n rows per page, the offset is (p - 1) * n.

The same thing in other databases#

LIMIT is used by MySQL, PostgreSQL and SQLite. Other databases spell it differently:

With offset in standard SQL:

SELECT name, salary
FROM employees
ORDER BY salary DESC
OFFSET 2 ROWS FETCH NEXT 2 ROWS ONLY;

MySQL also accepts the short form LIMIT 2, 2 (offset first, then count), which confuses many people. Prefer LIMIT 2 OFFSET 2.

Ties at the cut-off#

If two rows tie at the boundary, LIMIT picks one of them arbitrarily. To include every tied row, SQL Server has TOP 3 WITH TIES, standard SQL has FETCH FIRST 3 ROWS WITH TIES, and every database can use RANK.

Common mistakes#

Practice#

SELECT name, salary
FROM employees
ORDER BY salary
LIMIT 1 OFFSET 1;

name | salary
-----+-------
Asha | 52000

Key takeaway#