DISTINCT, LIMIT and TOP do not filter rows by content — they police the shape of the result: DISTINCT erases repeated rows, LIMIT caps how many can leave, and TOP is SQL Server's name for the same ceiling. Together they are the difference between a dataset and a deliverable.
What exactly does DISTINCT collapse?
DISTINCT reduces the result to unique rows — duplicates by value, not by row id. Over the bookshop roster it answers a familiar office-organising question: which departments actually have people?
SELECT DISTINCT department FROM staff;
department
----------
Buying
Sales
Cafe
Three rows from eight. Point DISTINCT at several columns and the guarantee shifts to unique combinations — pairs, not singletons. Sorted department by department, the roster announces where it is thin — three-plus groups appear, and one odd trailing group needs the last section to explain:
SELECT DISTINCT department, city FROM staff ORDER BY department, city;
department city
---------- ----------
Buying London
Buying Manchester
Cafe Leicester
Cafe Manchester
Sales NULL
Sales Leeds
Sales London
Notice the Sales row with no city: it sorts first inside its group, because SQLite treats a NULL as smaller than any text, and it gets a group of its own. Lesson 8's absences remain honest here: a NULL is one value for grouping purposes, always reported rather than averaged away. Anyone writing SELECT DISTINCT to mask a runaway join should read that sentence twice: DISTINCT fixes duplicates deserve suspicion first, because hiding them can hide a wrong join underneath.
How does LIMIT cap the output — and what decides which rows win?
LIMIT rests on the rows-that-are-left after WHERE and sorting. Payroll wants to see the three highest-paid rows, so sort first, then cap:
SELECT name, salary FROM staff ORDER BY salary DESC LIMIT 3;
name salary
------------- ------
Priya Raman 61000
Lena Marsh 54000
Dana Kowalski 46500
This triplet reads naturally, and it carries the clause artefact everyone forgets: which three rows survive is defined only after ORDER BY. Unordered, LIMIT 3 picks an arbitrary first three — SQLite scans in storage order, MySQL has no such promise, so any report leaning on unspecified order is flaky by construction, not occasionally. Ties at the cut edge are the other hazard: had the third salary been held by two people, the cap would still keep one of them by fiat. some engines (SQL Server with WITH TIES, PostgreSQL 13+ with FETCH FIRST) can be told to keep equal rows — know the engine before trusting a hard edge.
How do you page through what LIMIT skipped?
OFFSET skips that many survivors before counting starts — page two of the payroll leaderboard continues exactly where page one stopped:
SELECT name, salary FROM staff ORDER BY salary DESC LIMIT 3 OFFSET 3;
name salary
------------- ------
Aisha Zahra 32000
Marcus Bell 28000
Tom Whitfield 27500
Offset paging is honest but expensive at depth — page 500 must still decree the first 1497 rows to sort through, which is why serious paginated products lean on keyset pagination using the last-seen salary as the boundary. A second OFFSET wrinkle: each of two pages can shift if rows were inserted between requests, so a user paging while staff are hired can see someone twice or miss someone entirely. For practice data, neither matters; for real products, both do.
Pick your order of operations deliberately: distinct first or cap first? DISTINCT applied after a LIMIT — as it would when surrounding a subquery — caps rows before uniqueness collapses them, so the visible count can differ from expectations either direction. A one-line habit keeps this straight: apply DISTINCT to honesty (what classification exists?), LIMITs only when someone asked for a smaller deliverable, and never let the two repair a query you should instead read again.
What do other engines call LIMIT?
The same ceiling wears three dialects' outfits. SQL Server leads with TOP at the front of the SELECT, MySQL family stays with familiar LIMIT, and the SQL standard spells the pair OFFSET FETCH at the tail — PostgreSQL and recent SQL Server both honour that spelling too, which makes it the lowest-friction portable choice when you can assume modern engines:
-- SQL Server: SELECT TOP 3 name, salary FROM staff ORDER BY salary DESC;
-- MySQL / SQLite: the LIMIT 3 form verified above
-- SQL standard: SELECT name, salary FROM staff ORDER BY salary DESC OFFSET 0 ROWS FETCH FIRST 3 ROWS ONLY;
I marked this block as reference syntax on purpose — the three snippets are grammar documents, nothing was claimed run except the LIMIT outputs you saw above, which SQLite executed. When scheduling queries against an unfamiliar engine, take thirty seconds on its docs rather than its compatibility folklore.
Quick reference
- DISTINCT yields unique rows, or unique column combinations when given multiple columns.
- NULLs hold together as one distinct value — always visible, never averaged into thin air.
- LIMIT is only meaningful after ORDER BY; without a sort the kept rows are arbitrary.
- OFFSET pages but re-sorts everything before it; deep pages belong to keyset pagination.
- TOP (SQL Server) and OFFSET FETCH (standard) are LIMIT's dialectal siblings.
Skirmish drill: list every unique job title on the roster, cap any city scan you did earlier to four rows, and produce page two of an alphabetical name listing. Then — the interesting one — try DISTINCT city and order by city, and explain in one sentence why an unknown entry lands where it does.