Premium

LIMIT and OFFSET

Basic SQL
≈ 1 min read

LIMIT keeps only part of the result

LIMIT sets the maximum number of rows to return. It is useful for small result sets, data previews, and pagination. 

SELECT id, name
FROM products
ORDER BY id
LIMIT 10;

The query first defines ordering by id and then returns the first ten rows of that ordered result.

OFFSET skips rows

SELECT id, name
FROM products
ORDER BY id
LIMIT 10 OFFSET 20;

OFFSET 20 skips the first twenty rows, after which LIMIT 10 keeps the next ten. This pattern is often used for simple numbered pagination.

Predictable pagination needs a stable ORDER BY. If no ordering is specified, or several rows are indistinguishable by the chosen sort columns, page contents can be unstable. It is often useful to add a unique column as the final ordering criterion.

LIMIT and OFFSET are widely used, but the exact syntax for limiting results differs between database systems. When working with a specific database, check its dialect.

Back Next