✂️SELECT TOP / LIMIT / FETCH

Capping rows is not a filter substitute

LIMIT / TOP / FETCH slice the result after the rest of the query runs. They do not magically make scans cheaper, you still pay for joins and sorts unless the optimizer can prove early termination (rare). Pair caps with a selective WHERE whenever possible.

🌐Dialect cheat sheet (keep this near your editor)

Same idea, different spelling. Polyglot services should centralize pagination helpers.

Dialect	Pattern
PostgreSQL / MySQL / SQLite	… ORDER BY col LIMIT n OFFSET m
SQL Server	SELECT TOP (n) … ORDER BY col
ANSI SQL	… ORDER BY col OFFSET m ROWS FETCH NEXT n ROWS ONLY

OFFSET pagination

Page 2 with OFFSET 1000 still walks the first 1000 rows to discard them, CPU grows with page depth. For user feeds, prefer keyset pagination: WHERE (sort_key, id) > ($last_pair).

ORDER BY is mandatory for stable pages

Without a total order (including tie-breakers like primary key), two identical requests can return different slices when rows move between pages.

SQL

LIMIT + OFFSET (page 2, page size 3)

SELECT compound_id, smiles
FROM compounds
ORDER BY compound_id
LIMIT 3 OFFSET 3;

✓Illustrative result set

compound_id	smiles
4	CCO
5	CC(=O)O
6	c1ccccc1

⚡ Advanced / dialect note

Without a deterministic ORDER BY, which rows appear in the first page is undefined, tests may flap and pagination can skip or repeat rows when data changes between requests.

API hardening

Enforce a maximum LIMIT server-side even if clients request more, ad-hoc notebooks love LIMIT 999999 against production replicas.

TL;DR

LIMIT n / OFFSET m (PostgreSQL, MySQL) and TOP n / OFFSET-FETCH (SQL Server) cap rows returned after sorting. Without ORDER BY, “first n rows” is undefined. Deep OFFSET scans/skips many rows, use keyset pagination for large offsets. Always cap limits on public APIs to protect the database.