Chapter 02 · 11 min · Basic
Which duplicate survives
Every tutorial teaches deduplication as a function. DISTINCT removes duplicates. ROW_NUMBER keeps one per group. Done.
That is the easy half, and it is not the half you get paid for. On real data the duplicates are rarely identical, so before any function helps you have to answer a question nobody has written down: which of these rows is the real one?
Here is the version of that question you will actually face. Same person, two rows, and the choice you make moves the revenue report, the signup report and the customer count in three different directions. There is no correct answer. There is an answer you can defend.
The dataset
Six rider rows for four actual people, plus their payments. Two kinds of duplicate are in here on purpose, and they are not the same problem: mei@example.com appears twice with byte-identical details, while siti@example.com appears twice with a different name spelling, a different signup date, and — the part that matters — a different amount of money attached to each row.
Schema
| rider_id | int |
| text | |
| full_name | text |
| signed_up | date |
| home_city | text |
| payment_id | int |
| rider_id | int |
| paid_myr | numeric(8,2) |
| paid_at | date |
Example data
DISTINCT is the wrong reflex
The instinct when you see repeated people is SELECT DISTINCT. Run it and count the rows.
You still get six. DISTINCT compares whole rows, and every row here differs in at least rider_id, which is a primary key and therefore unique by construction. It did exactly what it promises and achieved nothing.
This is the general shape of the problem. DISTINCT deduplicates rows. You almost never want that. You want to deduplicate entities — one person, one order, one device — and the database has no idea which columns identify an entity. That is a judgement you supply.
So the first real question is never "how do I remove duplicates". It is: what makes two rows the same person? Here it is email. On another table it might be a phone number, or a name plus a birth date, or nothing reliable at all — in which case the honest answer to your manager is that the data cannot support the question.
Six rows, four people. What will after_distinct be?
Basic
The rest of this chapter is Basic
Learning SQL is free here, forever. This track is the paid half: what to do when the data is dirty, duplicated and undocumented, and somebody still wants a number.
RM 25/mo · cancel anytime
Still to come in this chapter
- 02Find the entity, then look at what disagrees
- 03Picking a survivor, and the tie that picks itself
- 04What each choice costs you
- 05Prove it held
- 06Now find them where you cannot see them