LIMIT & OFFSET

LIMIT caps the number of rows a query returns. OFFSET skips a number of rows before starting to return results. Together they are the classic way to implement pagination — showing users page 1, page 2, page 3 of a result set.

Dialect note

LIMIT / OFFSET is the syntax used by PostgreSQL, MySQL, and SQLite. SQL Server and Oracle use different syntax for the same idea, covered on the next page.

LIMIT — capping the row count

The 10 most expensive products

SQL

SELECT product_name, price
FROM products
ORDER BY price DESC
LIMIT 10;

ORDER BY before LIMIT matters enormously here — without a defined sort, “the first 10 rows” is not a meaningful, stable request.

OFFSET — skipping rows

Skip the first 10 rows

SQL

SELECT product_name, price
FROM products
ORDER BY price DESC
OFFSET 10;

Combining LIMIT and OFFSET for pagination

Page 3 of results, 10 rows per page

SQL

SELECT product_name, price
FROM products
ORDER BY price DESC
LIMIT 10 OFFSET 20;  -- skip the first 20 rows, return the next 10

In general, page n with a page size of p is LIMIT p OFFSET (n - 1) * p.

Large OFFSET values get slow

A large OFFSET does not let the database magically jump straight to the right spot — it typically still has to scan through (or at least count past) every skipped row before it can start returning results. OFFSET 1000000 can be dramatically slower than OFFSET 10, which becomes a real problem for deep pagination on large tables, like a user clicking through to page 5,000 of search results.

For large or frequently-paginated datasets, keyset pagination (also called cursor-based pagination) — where you remember the last row you saw and filter with WHERE id > last_seen_id instead of counting through an offset — scales far better. That technique deserves, and gets, its own dedicated treatment later in this series.