LIMIT & OFFSET

Often you only need part of a result: the five most expensive products, the latest ten orders, or page three of a search. LIMIT caps how many rows come back and OFFSET skips rows first. The syntax varies between databases, so this lesson covers the main forms.

LIMIT

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

Always pair LIMIT with ORDER BY. Without it, "the first three rows" means whichever three the database happens to return first.

OFFSET: skipping rows

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

This skips the three most expensive products and returns the next three — the 4th to 6th.

Pagination

To show page n with size rows per page, use LIMIT size OFFSET (n - 1) * size:

-- Page 2 of the product list, 4 per page
SELECT id, name
FROM products
ORDER BY name, id
LIMIT 4 OFFSET 4;

The sort must be unique (hence id as a tie-breaker), or rows can appear on two pages or none.

Dialect differences

SELECT name, price
FROM products
ORDER BY price DESC
OFFSET 3 ROWS
FETCH NEXT 3 ROWS ONLY;

SELECT TOP 3 name, price
FROM products
ORDER BY price DESC;

Ties at the boundary

If the 3rd and 4th rows have the same value, LIMIT 3 arbitrarily includes one and drops the other. The standard FETCH FIRST 3 ROWS WITH TIES (PostgreSQL, Oracle, SQL Server's TOP 3 WITH TIES) includes all tied rows. In SQLite you can achieve the same with window functions (lesson 34).

Large offsets are slow

To return page 1,000, the database still has to find and throw away the first 999 pages. For deep pagination, keyset pagination is far faster: remember the last row you showed and ask for rows after it.

-- Page 1 ended with 'Learning SQL Step by Step' (id 2)
SELECT id, name
FROM products
WHERE (name, id) > ('Learning SQL Step by Step', 2)
ORDER BY name, id
LIMIT 4;

This returns the same rows as page 2 above, without skipping anything. The row-value comparison (name, id) > (…, …) is supported by SQLite, PostgreSQL and MySQL. With an index on the sort columns, this is fast at any depth.

Try it yourself

Show the three most recently hired employees.