LIMIT / OFFSET / FETCH FIRST

Returns only the first n rows of the (sorted) result, optionally after skipping some.

Syntax

... ORDER BY ... LIMIT n [OFFSET m];
... ORDER BY ... OFFSET m ROWS FETCH FIRST n ROWS { ONLY | WITH TIES };

LIMIT cuts the result after the first n rows; OFFSET skips m rows first. They run last, after ORDER BY. Without ORDER BY "the first n rows" is arbitrary.

FETCH FIRST n ROWS ONLY is the SQL-standard spelling of LIMIT n; both work in PostgreSQL.
FETCH FIRST n ROWS WITH TIES also returns rows that tie with the last row on the ORDER BY keys (requires ORDER BY). Useful for "top 3 including ties".
LIMIT ALL or LIMIT NULL means no limit.
OFFSET pagination gets slower for deep pages (the skipped rows are still read) and can skip or repeat rows if data changes between pages. For large tables, page by key: WHERE id > last_seen_id ORDER BY id LIMIT 50.
For "top N per group" LIMIT is not enough — use ROW_NUMBER() or a LATERAL subquery.

Examples

Top 3 most expensive products starter

SELECT product_name, price
FROM products
ORDER BY price DESC, product_id
LIMIT 3;

Sort first, then cut. The id tie-breaker makes the top 3 deterministic.

Second page of 5 starter

SELECT order_id, order_date
FROM orders
ORDER BY order_id
LIMIT 5 OFFSET 5;

Skips orders 1–5 and returns the next five.

Ties included starter

SELECT customer_id, count(*) AS orders
FROM orders
GROUP BY customer_id
ORDER BY orders DESC
FETCH FIRST 2 ROWS WITH TIES;

Returns at least 2 rows; more if other customers tie with the second one.

Pitfalls

LIMIT without ORDER BY: the rows you get are not guaranteed and can change between runs.
Using LIMIT for top-N per category; it limits the whole result, not each group.
Deep OFFSET pagination on large tables (slow, and unstable while rows are inserted).

Other databases

For orientation only: SQL Quest executes PostgreSQL.

SQL Server: No LIMIT. Use SELECT TOP (n) ... or ORDER BY ... OFFSET m ROWS FETCH NEXT n ROWS ONLY (OFFSET/FETCH requires ORDER BY). TOP (n) WITH TIES exists.
Oracle: No LIMIT. Use FETCH FIRST n ROWS ONLY / WITH TIES (12c+); older code uses ROWNUM.
MySQL: LIMIT n OFFSET m or LIMIT m, n (offset first!). No FETCH FIRST and no WITH TIES.
BigQuery: LIMIT n OFFSET m; no FETCH FIRST.
Snowflake: Supports LIMIT/OFFSET, FETCH FIRST and TOP n.