LIMIT, FETCH FIRST and OFFSET

## The two numbers do different jobs Row limiting takes a result that the rest of the query has already computed and returns a contiguous slice of it. Two independent numbers describe that slice: - **OFFSET n** — skip the first `n` rows of the result and do not return them. - **LIMIT m** — of whatever remains, return at most `m` rows. ```sql SELECT id, total FROM orders ORDER BY id LIMIT 10 OFFSET 20; -- rows 21..30 of the id ordering ``` The skip always happens first, regardless of the order the two keywords are written in. `LIMIT 10 OFFSET 20` and `OFFSET 20 LIMIT 10` mean the same thing where both spellings are accepted; the count after `LIMIT` is never "the first ten, then skip twenty". ## Where limiting sits relative to the rest of the query Limiting is the very last thing that happens. Rows are produced by `FROM`, filtered by `WHERE`, grouped and filtered again by `GROUP BY`/`HAVING`, projected by the select list, sorted by `ORDER BY` — and only then sliced. That matters in practice: `LIMIT` cannot make a filter cheaper by "stopping early" from the author's point of view, and a `LIMIT` on the outer query does not restrict what a subquery inside it computes. If you want the ten highest totals, `ORDER BY total DESC LIMIT 10` gives them; `LIMIT 10` followed by a sort would be a different (and meaningless) query, which is why the standard fixes the order of these clauses. ## Page arithmetic The common use is paging a list. With one-based page numbers and a fixed page size: ```sql -- page 4, 25 rows per page SELECT id, title FROM articles ORDER BY published_at DESC, id DESC LIMIT 25 OFFSET 75; -- (4 - 1) * 25 ``` Off-by-one bugs here are almost always the page-number base: page 1 must use `OFFSET 0`, not `OFFSET 25`. Note the second sort key — see below. ## What LIMIT does not promise **It is an upper bound.** `LIMIT 10` returns ten rows *or fewer*. A query whose result holds 24 rows, asked for `LIMIT 10 OFFSET 20`, returns four. Application code that assumes a full page and infers "there is no next page" from a short page is usually right, but code that assumes exactly `m` rows is wrong. **Overshooting is not an error.** `OFFSET 1000` against a 30-row result returns the empty set. There is no exception, no warning, and no clamping to the last page — an empty page is what a caller past the end sees. **It does not create an order.** `LIMIT` slices whatever order the engine produced. Without `ORDER BY`, no order is defined, so "the first ten rows" is whatever the plan happened to emit. Even *with* `ORDER BY`, if the sort key is not unique the rows tied on that key may come back in any order, so the boundary between page 3 and page 4 can move between requests. The fix is to make the ordering total by appending a unique column, typically the primary key: ```sql ORDER BY published_at DESC, id DESC ``` **The offset is a position, not an anchor.** It counts rows in a result recomputed from scratch on every request, so it says nothing about *which* rows you saw last time. ## Spelling and portability `LIMIT ... OFFSET ...` is the widely implemented spelling but is not the ISO standard one. Standard SQL writes the same slice as `OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY`, placed after `ORDER BY`. Engines differ on which forms they accept and on whether `OFFSET` may appear with no row-count limit at all, so if a query has to move between engines, check the target's documentation rather than assuming. ## Reading a query quickly When you see `ORDER BY <keys> LIMIT m OFFSET n`, read it as: sort by the keys, drop `n` rows, keep the next `m`. Then ask the two follow-up questions an interviewer is waiting for — is the ordering total, and does the caller need the same rows to stay on the same page while data changes underneath? The first is answered by adding a unique tie-breaker; the second is a property of offset-based paging itself.