Lesson Description
Remove duplicates with DISTINCT and choose the correct uniqueness grain for the question being asked. You will learn when DISTINCT is the right tool and when grouping logic is a better fit.
Lesson 04
Lesson Description
Remove duplicates with DISTINCT and choose the correct uniqueness grain for the question being asked. You will learn when DISTINCT is the right tool and when grouping logic is a better fit.
Easy Project
Mini project: Turn this lesson into a real-world query artifact by writing one clean business report query and validating output quality.
Many questions are about which values exist, not about individual rows. "Which cities do our patients come from?" "Which customers have placed at least one order?" "How many countries do we sell to?" If you simply select the column, you get one row per record in the table, full of repeats. The hospital has 30 patients but only a handful of cities, so SELECT city FROM patients gives you 30 rows with Seattle appearing again and again.
DISTINCT removes duplicate rows from the result, so each unique value (or combination of values) appears exactly once. It is one of the quickest tools for exploring an unfamiliar table and for checking how many different values a column really holds.
SELECT DISTINCT column1, column2
FROM table_name;
DISTINCT goes directly after SELECT and applies to the whole row of output, not just the first column. There is also a counting form:
SELECT COUNT(DISTINCT column1)
FROM table_name;
SELECT DISTINCT city
FROM patients
ORDER BY city;
Instead of 30 rows, you get 5: Bellevue, Portland, Seattle, Spokane and Tacoma. Without DISTINCT, Seattle alone would appear 10 times, because 10 of the 30 patients live there.
This is often the first thing an analyst runs against a new table. Before writing a filter like WHERE city = 'Seattle', you want to know how the values are actually spelled.
The same check on the store's orders table tells you a lot about the business:
SELECT DISTINCT status
FROM orders
ORDER BY status;
Four statuses exist: cancelled, delivered, pending and shipped. If someone asks you to report "returned" orders, you now know there is no such status in the data.
Each order row has a customer_id. Selecting it plainly gives you 50 rows, one per order, with repeat buyers listed several times. DISTINCT turns it into a list of customers who have ordered:
SELECT DISTINCT customer_id
FROM orders
ORDER BY customer_id;
That returns 20 rows. The customers table has 22 people, so you have just learned that 2 customers signed up but never placed an order. That kind of quick insight is where DISTINCT shines. (Finding exactly which 2 is a job for joins, later in the curriculum.)
With several columns, DISTINCT keeps one row for each unique combination. In the hospital:
SELECT DISTINCT city, gender
FROM patients
ORDER BY city, gender;
This returns 9 rows. Bellevue, Seattle, Spokane and Tacoma each appear twice (once for F and once for M), but Portland appears only once, with F. You have discovered that every Portland patient in the sandbox is female, something you would not see from either column alone.
The store sandbox has a good example of why the combination matters. Look closely at the products:
| Query | Rows |
|---|---|
SELECT name FROM products | 27 |
SELECT DISTINCT name FROM products | 26 |
SELECT DISTINCT name, price FROM products | 26 |
SELECT DISTINCT name, stock FROM products | 27 |
There are two products called "Yoga Mat" (ids 23 and 26), both priced 29.99 but with different stock levels (100 and 40). By name, or by name and price, they collapse into one row. Add stock and they become different combinations again, so both rows come back. Every column you add to a DISTINCT list can only keep the row count the same or make it larger.
Often you want the number of unique values, not the list. COUNT(DISTINCT ...) does that in one step:
SELECT
COUNT(*) AS customers,
COUNT(DISTINCT country) AS countries
FROM customers;
The answer is a single row: 22 customers spread across 11 countries. COUNT(*) counts rows; COUNT(DISTINCT country) counts different country values. You will meet COUNT and the other aggregate functions properly in the Aggregations course; for now, treat COUNT(DISTINCT col) as "how many unique values".
Sometimes you want one complete row for each distinct value, for example "the most recently hired employee in each department". Plain DISTINCT cannot do that, because adding names and dates to the list makes every row unique. PostgreSQL has an extension for exactly this case:
SELECT DISTINCT ON (department_id)
department_id, first_name, last_name, hire_date
FROM employees
ORDER BY department_id, hire_date DESC;
You get 9 rows, one per department that has employees. For department 80 (Sales) it is Eleni Zlotkey, hired 2008-01-29; for department 100 (Finance) it is Luis Popp, hired 2007-12-07.
The rule is: DISTINCT ON (x) keeps the first row for each value of x, and "first" is decided by ORDER BY. The ORDER BY must start with the same column(s) as DISTINCT ON, and the columns after that decide which row wins. Here hire_date DESC makes the newest hire win. Change it to hire_date ASC and you get the longest-serving person in each department instead.
In the logical order you have been following (FROM, WHERE, SELECT, DISTINCT, ORDER BY, LIMIT), DISTINCT runs after the output columns are computed and before sorting. Two consequences:
DISTINCT compares the values you selected, including calculated columns and aliases. It never looks at columns you left out.DISTINCT, ORDER BY can only use columns that are in the output, because the other columns no longer exist as a single value per row. (Which patient's last name should PostgreSQL use to sort the row "Seattle"?)For DISTINCT, all NULLs count as the same value, so they collapse into one row. In HR, SELECT DISTINCT manager_id FROM employees returns 10 rows: 9 real manager ids plus one NULL (Steven King has no manager). But COUNT(DISTINCT manager_id) returns 9, because COUNT of a column ignores NULLs. Same data, two different numbers. Keep that in mind when you compare a list with a count.
To find duplicates, PostgreSQL usually either sorts the rows or builds a hash table of them. On large tables that work is not free, so add DISTINCT because you need it, not as a reflex.
SELECT DISTINCT (first_name), last_name
FROM employees;
The parentheses look like they limit DISTINCT to first_name, but they are just grouping one expression. DISTINCT still applies to the whole row. There are 35 distinct first names in HR (Alexander, David, John, Karen and Peter each appear twice), but this query returns all 40 rows, because every first and last name pair is unique. If you want one row per first name with some other column attached, you need DISTINCT ON or GROUP BY.
SELECT DISTINCT city
FROM patients
ORDER BY last_name;
PostgreSQL refuses with for SELECT DISTINCT, ORDER BY expressions must appear in select list. Sort by city, or add the sort column to the list (which changes what counts as distinct).
When a query that joins tables returns duplicates you did not expect, adding DISTINCT makes the symptom disappear, but the cause (usually a join on the wrong column) is still there and can make totals wrong elsewhere. You will see this in the Joins course. Use DISTINCT when duplicates are a real feature of the data, not to paper over a mistake.
SELECT DISTINCT and COUNT(DISTINCT col) work in MySQL, SQL Server, SQLite and every other major database.DISTINCT ON is PostgreSQL only. Elsewhere you get the same result with a window function such as ROW_NUMBER(), covered in the Window Functions course.COUNT(DISTINCT a, b) across several columns. PostgreSQL does not accept that form, but you can wrap the columns in parentheses to count distinct pairs: COUNT(DISTINCT (city, gender)) returns 9 for the patients table, matching Example 3.The exercises below this lesson ask you to list unique values from HR and store tables, count how many different doctor specialties exist in the hospital, and find unique pairs of two columns. Remember to sort the output when the prompt asks for it, and think about whether you need the list of values or just how many there are.
SELECT DISTINCT removes duplicate output rows; it considers every selected column together.DISTINCT list can only keep the row count the same or increase it.COUNT(DISTINCT col) counts unique non-NULL values, while DISTINCT lists NULL as one value.DISTINCT, ORDER BY may only use selected columns.DISTINCT ON (col) (PostgreSQL only) keeps one full row per value, chosen by ORDER BY.DISTINCT collapses duplicate rows into one, which answers questions like "which departments have staff" or "which countries do customers come from". It applies to the whole selected row, so it is a tool for listing unique combinations, not for fixing queries that return too many rows.
Unique values in one column
SELECT DISTINCT country
FROM customers
ORDER BY country;One row per country, regardless of how many customers live there.
Unique combinations
SELECT DISTINCT department_id, job_id
FROM employees
ORDER BY department_id, job_id;DISTINCT considers both columns together, so each department/job pairing appears once.
SELECT DISTINCT department_id FROM employees return if three employees have no department?One row with NULL alongside the real department ids. DISTINCT groups all NULLs together.
Concept check
3 quick questions. One at a time. Instant score at the end.
Return the distinct department_id values from the employees table, ordered ascending.
No results yet
Run a query to see results here
From the store sandbox, return the distinct category_id values from the products table, ordered ascending.
No results yet
Run a query to see results here
From the hospital sandbox, return a single value specialty_count — the number of distinct specialties across all doctors.
No results yet
Run a query to see results here
Return distinct (department_id, job_id) pairs from the employees table, ordered by department_id, then job_id.
No results yet
Run a query to see results here