SQL / SORTING, LIMITING, AND BRANCHING OUTPUT
Removing duplicates with DISTINCT
Remove duplicate rows with SELECT DISTINCT, predict how many rows survive as you add columns, and tell real duplicates from ones a join created.
What you will learn
- Dedupe on a column pair by selecting exactly those columns: DISTINCT keys the whole list
- Predict row counts: adding a column to the select list can only add distinct rows
- Explain why SELECT DISTINCT x keeps one NULL row but COUNT(DISTINCT x) ignores NULLs
- Swap DISTINCT for GROUP BY with COUNT(*) to see how many rows each key absorbed
Understanding Removing duplicates with DISTINCT
DISTINCT is a modifier on the entire select list, not on one column. The engine builds each output row first, then compares complete rows and keeps a single copy of each set of rows that compare equal. That is why SELECT DISTINCT city, priority returns one row per (city, priority) pair: every column you add gives rows one more way to differ, so widening the select list can hold the row count steady or raise it, never lower it.
The equality DISTINCT uses is not the = of a WHERE clause. Under =, NULL = NULL evaluates to unknown, but for deduplication and grouping SQL treats two NULLs as the same value, so a hundred rows whose only selected column is NULL collapse into one row containing NULL. COUNT(DISTINCT x) behaves differently again, because aggregate functions discard NULL inputs before deduplicating: it can report one value where SELECT DISTINCT x returns two rows.
Put DISTINCT in the pipeline and the rest follows: rows are read, WHERE filters, select-list expressions are computed, DISTINCT drops duplicate rows, then ORDER BY sorts and LIMIT cuts. So DISTINCT sees computed values (UPPER(city) merges case variants the raw column would keep apart), LIMIT 10 yields ten deduplicated rows rather than ten raw ones, and ORDER BY can only reference values that survived, which is why a column omitted from the select list has no defined value to sort by. Deduplicating also forces a sort or a hash over every output row, the same work as GROUP BY on every selected column.
CREATE TABLE ticket (id INTEGER, city TEXT, priority TEXT);
INSERT INTO ticket VALUES
(1, 'Lisbon', 'high'),
(2, 'Lisbon', 'high'),
(3, 'Lisbon', 'low'),
(4, 'Oslo', 'high'),
(5, 'Oslo', 'high');
-- one row per distinct city
SELECT DISTINCT city
FROM ticket
ORDER BY city;
-- one row per distinct (city, priority) pair
SELECT DISTINCT city, priority
FROM ticket
ORDER BY city, priority;DISTINCT removes duplicate rows, not duplicate values in one column, because it compares the whole select list.
Worked examples
Parentheses do not scope DISTINCT
Shows that DISTINCT(city) is not a function call restricting dedup to one column.
WITH ticket(city, priority) AS (
SELECT 'Lisbon', 'high' UNION ALL
SELECT 'Lisbon', 'high' UNION ALL
SELECT 'Lisbon', 'low' UNION ALL
SELECT 'Oslo', 'high' UNION ALL
SELECT 'Oslo', 'high'
)
SELECT DISTINCT (city), priority
FROM ticket
ORDER BY city, priority;Example explained
Line 1UNION ALL keeps the repeated rows, so the CTE really does hold five rows.
Line 2(city) parses as a parenthesized expression, so the select list is still city, priority.
Line 3The result is the three (city, priority) pairs, identical to writing DISTINCT city, priority.
Line 4There is no syntax that deduplicates one column while returning others untouched.
NULLs collapse for DISTINCT but vanish for COUNT(DISTINCT)
Contrasts how row deduplication and the aggregate treat NULL inputs.
WITH reading(sensor, note) AS (
SELECT 'a', NULL UNION ALL
SELECT 'a', NULL UNION ALL
SELECT 'b', 'ok' UNION ALL
SELECT 'b', 'ok' UNION ALL
SELECT 'c', 'ok'
)
SELECT COUNT(*) AS rows_total,
COUNT(note) AS notes_not_null,
COUNT(DISTINCT note) AS notes_distinct,
(SELECT COUNT(*) FROM (SELECT DISTINCT note FROM reading) d) AS distinct_rows
FROM reading;Example explained
Line 1COUNT(*) counts rows without looking at values, so the two NULL notes are included: 5.
Line 2COUNT(note) skips NULL inputs, leaving the three 'ok' rows: 3.
Line 3COUNT(DISTINCT note) skips NULLs first and then deduplicates 'ok', reporting 1.
Line 4SELECT DISTINCT note keeps one NULL row next to 'ok', so distinct_rows is 2, exactly one more.
See what DISTINCT threw away
Uses GROUP BY with COUNT(*) to reveal how many raw rows each deduplicated row absorbed.
WITH ticket(city, priority) AS (
SELECT 'Lisbon', 'high' UNION ALL
SELECT 'Lisbon', 'high' UNION ALL
SELECT 'Lisbon', 'low' UNION ALL
SELECT 'Oslo', 'high' UNION ALL
SELECT 'Oslo', 'high'
)
SELECT city, priority, COUNT(*) AS collapsed_rows
FROM ticket
GROUP BY city, priority
ORDER BY city, priority;Example explained
Line 1GROUP BY city, priority produces the same three keys DISTINCT returned, so DISTINCT is a grouping with no aggregates.
Line 2COUNT(*) exposes the multiplicity DISTINCT discards: 2 + 1 + 2 adds back to the five raw rows.
Line 3A count of 2 where you expected 1 tells you the duplicates are real data or a join fan-out, not a display artifact.
DISTINCT applies to computed values
Shows dedup happening on the result of an expression rather than the stored column.
WITH ticket(city) AS (
SELECT 'Oslo' UNION ALL
SELECT 'oslo' UNION ALL
SELECT 'OSLO' UNION ALL
SELECT 'Lisbon'
)
SELECT DISTINCT UPPER(city) AS city_key
FROM ticket
ORDER BY city_key;Example explained
Line 1UPPER runs while the select list is being computed, before any comparison happens.
Line 2Dedup then sees OSLO three times and keeps one row, so four input rows become two.
Line 3SELECT DISTINCT city on the same data returns four rows in Postgres and SQLite, whose default string comparison is case sensitive.
Line 4ORDER BY city_key is allowed because city_key is in the select list and therefore survives dedup.
Important notes
With DISTINCT present, Postgres and MySQL reject ORDER BY on a column that is not in the select list, because the sort key need not survive dedup; SQLite accepts it and sorts by whichever value it happened to keep.
Whether two values count as duplicates depends on type and collation: under MySQL's common case-insensitive collations 'ok' and 'OK' collapse into one row, while Postgres and SQLite keep them apart, and numeric 1 and 1.0 compare equal everywhere.
Common mistakes
Writing SELECT DISTINCT(customer_id), order_date and expecting only customer_id to be deduplicated; the parentheses are plain grouping, DISTINCT still keys on both columns, so each different date yields another row and the dedup looks broken.
Adding an id or created_at column to the select list for reference; it makes every row unique, DISTINCT then removes nothing, and the duplicates come back with no error to warn you.
Using DISTINCT to cover up rows multiplied by a join and then aggregating; SUM and COUNT are computed over the multiplied rows before DISTINCT ever sees the result, so the totals stay inflated while the row list looks clean.
Try it yourself
Change, predict, then run
Create a five-row table of (city, priority) containing one exactly repeated pair and two rows whose priority is NULL, then run SELECT COUNT(*), SELECT DISTINCT city, priority, and SELECT COUNT(DISTINCT priority). Explain in a comment why the last two disagree about the NULL rows.
Open the SQL workspaceCheck your understanding
A table signup(email, plan, signed_up_at) holds 1000 rows and 400 distinct emails, and every signup has its own timestamp. What does SELECT DISTINCT email, signed_up_at FROM signup return?
- Exactly 400 rows, one per distinct email
- 400 rows, with signed_up_at taken from the earliest signup for each email
- Up to 1000 rows, one per distinct (email, signed_up_at) pair
- An error, because signed_up_at is not listed after DISTINCT
Show answer
DISTINCT compares the whole output row, so the key here is the pair; because timestamps differ, almost every row is already unique and the result stays near 1000. Answer 0 assumes DISTINCT binds only to the first column, the most common misconception; getting one row per email while still showing a timestamp requires GROUP BY email with MIN(signed_up_at) or a window function, not DISTINCT.