Show each value once
Remove repeated rows from an answer with DISTINCT. About 6 minutes.
The same value, over and over
Ask the library which genres it stocks and the plain answer lists Novel forty times, once for every novel. You wanted the list of genres, not the list of books.
Add DISTINCT right after SELECT and any row that already appeared in the answer is skipped. The table itself is untouched. Only the answer is trimmed.
SELECT DISTINCT genre FROM books;
Distinct applies to the whole row
With one column it is easy: each different value once. With two, DISTINCT looks at the pair. Two rows are repeats only when both columns match.
So SELECT DISTINCT genre, year can return Novel twice, if the years differ. Choose the columns first; DISTINCT then removes rows that look the same.
| id | title | genre |
|---|---|---|
| 1 | Salt Road | Novel |
| 2 | Paper Boats | Poetry |
| 3 | Lamp Street | Novel |
| 4 | Rain Letters | Stories |
| 5 | Old Fort | Poetry |
Novel: the first time it appears, so it goes into the answer.
What does SELECT DISTINCT city, branch FROM students return?
- AEach different city and branch pair, once
- BEach city once, then each branch once
- CEvery student, since every row has an id
Show the answer
Each different city and branch pair, once. DISTINCT compares the whole row of the selected columns.
Cities we serve
The orders table has one row per order. Show every city that has at least one order, each city once, A to Z.
orders(id, customer, city, amount)
Up to 2,000 orders