SQL DISTINCT: Remove Duplicate Rows
SELECT DISTINCT keeps one copy of each value. Turn forty customer rows into a unique list of cities, sort it, and find which customers ordered in December, on a sample online shop database.
Step by step
Unique values
- Each value once. Which cities do our customers live in? If you just select the city, you get the same city again and again. Distinct keeps one copy of each. Let's try it.
- The customers table. The customers table has a city for every customer.
Every city
- Every row. Select the city from customers. Forty rows, one per customer. Norwich alone shows up seven times. That's not a list of cities.
SELECT city FROM customers;
DISTINCT
- DISTINCT. Add the word distinct, right after select. Thirteen rows. Twelve cities, plus one empty row, because one customer has no city. Distinct keeps one empty value too.
SELECT DISTINCT city
Sort it
- Sort the list. A list is easier to read in order. Add order by city before the semicolon. Now the cities are in alphabetical order. The empty one comes first, so it's easy to spot.
ORDER BY city;
Who ordered
- Who ordered?. Distinct is handy for questions like this. Which customers ordered in December? Start with the customer ID of every December order. Twelve orders. But some customers ordered more than once, so this isn't a count of people. Add distinct again. Ten rows. Twelve orders came from ten different customer I Ds.
SELECT customer_id FROM orders WHERE order_date >= '2025-12-01';SELECT DISTINCT customer_id
Recap
- DISTINCT. So, distinct goes right after select and keeps one copy of each value. Empty values count as one value. Use it to turn a long column into a clean list.
