The DISTINCT, the LIMIT and the TOP do not filter the rows by their content. They police the shape of the result. The DISTINCT erases the repeated rows, the LIMIT caps how many of them can leave, and the TOP is the SQL Server's name for the same ceiling. Together they are the difference between a dataset and a deliverable.
The previous lesson in this module covered "How Do NULLs Behave in Comparisons and Filters?", and this lesson is the last one in the SQL SELECT Queries module.
What exactly does the DISTINCT collapse?
The DISTINCT reduces the result to the unique rows. It removes the duplicates by value and not by the row id. Over the bookshop roster it answers a familiar office-organising question, which is which departments actually have people.
SELECT DISTINCT department FROM staff;department
----------
Buying
Sales
CafeThree rows come from eight. You point the DISTINCT at several columns, and the guarantee shifts to the unique combinations. You get the pairs and not the singletons. Sorted department by department, the roster announces where it is thin. The groups of three or more 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 LondonNotice 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. The absences of lesson 8 remain honest here. A NULL is one value for the grouping purposes, and it is always reported rather than averaged away.
Anybody who is writing a SELECT DISTINCT to mask a runaway join should read that sentence twice. The DISTINCT fixes the duplicates, but the duplicates deserve the suspicion first, because hiding them can hide a wrong join underneath.
How does the LIMIT cap the output, and what decides which rows win?
The LIMIT rests on the rows that are left after the WHERE and the sorting. The payroll wants to see the three highest-paid rows, so you sort first and then you cap.
SELECT name, salary FROM staff ORDER BY salary DESC LIMIT 3;name salary
------------- ------
Priya Raman 61000
Lena Marsh 54000
Dana Kowalski 46500That triplet reads naturally, and it carries the clause artefact that everybody forgets. Which three rows survive is defined only after the ORDER BY. Without the sort, the LIMIT 3 picks an arbitrary first three. SQLite scans in the storage order and MySQL has no such promise, so any report that leans on an unspecified order is flaky by construction and not only occasionally.
The ties at the cut edge are the other hazard. If the third salary had been held by two people, the cap would still keep one of them by fiat. Some engines, such as the SQL Server with the WITH TIES, and the PostgreSQL 13 and later with the FETCH FIRST, can be told to keep the equal rows. You know the engine before you trust a hard edge.
How do you page through what the LIMIT skipped?
The OFFSET skips that many survivors before the counting starts, so the page two of the payroll leaderboard continues exactly where the 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 27500The offset paging is honest but it is expensive at the depth. The page 500 must still sort through the first 1497 rows, which is why the serious paginated products lean on the keyset pagination, using the last-seen salary as the boundary.
There is a second OFFSET wrinkle. Each of the two pages can shift if the rows were inserted between the requests, so a user who is paging while the staff are being hired can see somebody twice or miss somebody entirely. For the practice data, neither of those matters. For the real products, both of them do.
You pick your order of operations deliberately. Is the DISTINCT first, or is the cap first? The DISTINCT that is applied after a LIMIT, as it would be when it surrounds a subquery, caps the rows before the uniqueness collapses them, so the visible count can differ from the expectations in either direction.
There is a one-line habit that keeps this straight. You apply the DISTINCT to the honesty, with the question of which classifications exist. You apply the LIMIT only when somebody asked for a smaller deliverable. And you never let the two of them repair a query that you should instead read again.
What do the other engines call the LIMIT?
The same ceiling wears the three dialects' outfits. The SQL Server leads with the TOP at the front of the SELECT. The MySQL family stays with the familiar LIMIT. And the SQL standard spells the pair as OFFSET FETCH at the tail. PostgreSQL and the recent SQL Server both honour that spelling too, which makes it the lowest-friction portable choice when you can assume the 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 the reference syntax on purpose. The three snippets are grammar documents. Nothing was claimed to run, except the LIMIT outputs that you saw above, which SQLite executed.
When you are scheduling the queries against an unfamiliar engine, take thirty seconds on its documentation rather than on its compatibility folklore.
Quick reference
- The DISTINCT yields the unique rows, or the unique column combinations when you give it the multiple columns.
- The NULLs hold together as one distinct value. They are always visible and never averaged into the thin air.
- The LIMIT is only meaningful after the ORDER BY. Without a sort, the rows that are kept are arbitrary.
- The OFFSET pages, but it re-sorts everything before it, and the deep pages belong to the keyset pagination.
- The TOP of the SQL Server and the OFFSET FETCH of the standard are the dialectal siblings of the LIMIT.
Skirmish drill: list every unique job title on the roster, cap any city scan that you did earlier to four rows, and produce the page two of an alphabetical name listing. Then do the interesting one. Try the DISTINCT city and order it by the city, and explain in one sentence why an unknown entry lands where it does.