{"course":{"slug":"data-and-sql","title":"Data, SQL and Getting to the Answer","url":"https://addaly.com/learn/data-and-sql","level":"beginner","track":"build"},"lesson":{"slug":"duplicates-and-which-to-keep","title":"Duplicates: finding them, and deciding which row survives","url":"https://addaly.com/learn/data-and-sql/duplicates-and-which-to-keep","position":49,"of":66,"minutes":8,"answer":"Find duplicates with GROUP BY and HAVING, keep one with row_number over a stated ordering and a tiebreaker, count what you dropped.","keyIdea":"Find duplicates with GROUP BY and HAVING, keep one with row_number over a stated ordering and a tiebreaker, count what you dropped, and treat fuzzy matching as a judgement whose false merges cost more than its misses."},"body":{"format":"markdown","text":"## Three kinds of duplicate\n\nThe earlier cleaning lesson made the point that whether two rows are \"the same\" is a business question. This lesson is about the mechanics once the question is answered, and there are three different mechanics because there are three different kinds of duplicate.\n\n**Exact duplicates** are rows identical in every column. They come from a file pasted twice or a job that ran twice. **Key duplicates** share the identifying columns and differ elsewhere: the same payment reference with two timestamps, because a retry recorded a second row. **Near duplicates** share nothing exactly and are still the same thing: `ada@example.com` and `Ada@Example.com`, or `+91 98765 43210` and `9876543210`.\n\n## Finding exact and key duplicates\n\n```sql\n-- exact: group by everything\nSELECT reference, paid_on, amount, city, count(*)\nFROM raw_payments\nGROUP BY reference, paid_on, amount, city\nHAVING count(*) > 1;\n\n-- by key: group by what should be unique\nSELECT reference, count(*), min(loaded_at), max(loaded_at)\nFROM raw_payments\nGROUP BY reference\nHAVING count(*) > 1;\n```\n\n> Figure: Two kinds of duplicate, and what each tool does with them. A two by two grid, rows Identical rows and Same key only, columns SELECT DISTINCT and row_number = 1: Identical rows and SELECT DISTINCT: Collapsed; Identical rows and row_number = 1: One kept; Same key only and SELECT DISTINCT: Both survive; Same key only and row_number = 1: One kept. DISTINCT keeps any row that differs in any column, so a retry that differs by one timestamp survives while two genuinely separate payments that happen to match are merged. It is wrong in both directions at once. A ranking with a stated ordering is a decision you can explain.\n\nThe second query's `min` and `max` of the load time tell you whether the duplicates came in one file or across two, which is usually the whole diagnosis. Look at ten of them before deciding anything.\n\n## Keeping one, by a rule you can state\n\nThe rule is nearly always \"the latest\" or \"the first\", and a window function applies it:\n\n```sql\nCREATE VIEW payments_deduped AS\nSELECT reference, paid_on, amount, city\nFROM (\n  SELECT *,\n         row_number() OVER (PARTITION BY reference\n                            ORDER BY loaded_at DESC, ctid DESC) AS rn\n  FROM raw_payments\n) t\nWHERE rn = 1;\n```\n\nWithin each reference, rows are numbered from the most recently loaded, and only number 1 survives. The `ctid` tiebreaker (the physical row address in Postgres; use a serial id elsewhere) makes the choice deterministic when two rows share a load time, which the ranking lesson explained is not optional. `DISTINCT` cannot do this job, because it keeps rows that differ in any column and has no notion of \"latest\".\n\nThe rows that did not survive are `WHERE rn > 1`, and a count of them, by load file, goes into the load log. A deduplication that cannot say how many rows it removed is a filter, not a decision.\n\n## Deleting in place\n\nIf the duplicates are in a real table rather than a raw staging table, and the decision is to remove them permanently:\n\n```sql\nDELETE FROM payments\nWHERE id IN (\n  SELECT id FROM (\n    SELECT id, row_number() OVER (PARTITION BY reference ORDER BY created_at DESC, id DESC) AS rn\n    FROM payments\n  ) t WHERE rn > 1\n);\n```\n\nRun the inner SELECT first and count it. Then run the DELETE inside a transaction, compare the row count it reports with that count, and only then commit. The module on changing data safely has the full discipline; the short version is that a DELETE without a rehearsal is how a year of payments disappears.\n\n## Near duplicates\n\nNormalising the key, as the previous lesson showed, turns most near duplicates into exact ones: lower-case the email, strip the phone to digits, and the `GROUP BY` finds them. What remains is fuzzy: `Priya Sharma` and `Priya Sharmaa`, `Bombay Traders` and `Bombay Trading Co`. Postgres's `pg_trgm` extension gives `similarity(a, b)`, a score from 0 to 1 based on shared three-letter fragments, and `levenshtein(a, b)` from `fuzzystrmatch` counts the edits between them.\n\n```sql\nSELECT a.name, b.name, similarity(a.name, b.name)\nFROM customers a JOIN customers b ON a.id < b.id\nWHERE similarity(a.name, b.name) > 0.6;\n```\n\nThis is a self-join over every pair, which is a million comparisons for a thousand customers; on a hundred thousand it needs the extension's index or a blocking step that only compares names sharing a first letter and a city. And the threshold is a judgement with a cost on both sides. Set it high and `Sharma` and `Sharmaa` stay apart; set it low and two different people called Priya Sharma are merged, which loses a customer's history into somebody else's and is far harder to undo than a missed match. Review a sample of the proposed merges by hand, log every merge with both original ids, and merge fewer rather than more.\n\n## The habit\n\nSay the rule out loud before the query: \"one row per reference, the most recently loaded one wins, ties broken by id\". If you cannot say the rule, you are not deduplicating; you are guessing with a window function."},"check":{"question":"A team removes duplicate payments with `SELECT DISTINCT * FROM raw_payments`. Retried payments, which share a reference but differ by a few seconds in `paid_at`, all survive. Why, and what should replace it?","options":[{"text":"DISTINCT compares only the first column, so it keeps rows whose references match but whose later columns differ; adding `reference` to an ORDER BY clause before the DISTINCT fixes it.","correct":false,"tempting":"DISTINCT compares every selected column, not the first; ORDER BY changes presentation order, not which rows are kept."},{"text":"DISTINCT keeps every row that differs in any column, and the retries differ in `paid_at`; a `row_number()` partitioned by reference and filtered to 1 keeps one per reference.","correct":true},{"text":"DISTINCT works but the retries were loaded from a second file, and DISTINCT only removes duplicates within a single load batch.","correct":false,"tempting":"DISTINCT has no notion of load batches; it compares the rows it is given regardless of origin."},{"text":"The `paid_at` column is a timestamptz, and DISTINCT treats timestamps that differ in time zone display as unequal even when they are the same instant.","correct":false,"tempting":"timestamptz equality compares instants, and in any case the retries are genuinely a few seconds apart, not the same instant."}]},"prev":{"slug":"strings-trim-case-and-unicode","title":"Text that looks identical and is not","url":"https://addaly.com/learn/data-and-sql/strings-trim-case-and-unicode"},"next":{"slug":"validating-a-cleaned-table","title":"Assertions: queries that return no rows when everything is right","url":"https://addaly.com/learn/data-and-sql/validating-a-cleaned-table"},"authorship":"Founder and series editor: Dr Mustafa Mun. Written by AI models to a written standard. Exam keys checked blind by a second model: 3,665 of 3,666 agreed. Academic review invited.","review":{"status":"Draft, open for academic review"},"cite":"Addaly (2026) 'Duplicates: finding them, and deciding which row survives', in Data, SQL and Getting to the Answer. Edited by M. Mun. Addaly. Available at: https://addaly.com/learn/data-and-sql/duplicates-and-which-to-keep (Accessed: 13 October 2026).","licence":{"name":"CC BY-NC-SA 4.0","url":"https://creativecommons.org/licenses/by-nc-sa/4.0/"},"reader":{"schema":1,"title":"Duplicates: finding them, and deciding which row survives","courseTitle":"Data, SQL and Getting to the Answer","module":{"index":5,"slug":"data-that-arrives-dirty","title":"Data that arrives dirty"},"answer":null,"keyIdea":"Find duplicates with GROUP BY and HAVING, keep one with row_number over a stated ordering and a tiebreaker, count what you dropped, and treat fuzzy matching as a judgement whose false merges cost more than its misses.","hero":{"kind":"figure","figure":{"t":"figure","label":"Figure","figure":{"cols":["SELECT DISTINCT","row_number = 1"],"rows":["Identical rows","Same key only"],"type":"matrix","cells":[["Collapsed","One kept"],["Both survive","One kept"]],"title":"Two kinds of duplicate, and what each tool does with them","caption":"DISTINCT keeps any row that differs in any column, so a retry that differs by one timestamp survives while two genuinely separate payments that happen to match are merged. It is wrong in both directions at once. A ranking with a stated ordering is a decision you can explain.","cellLabels":[["real repeats die too","by a rule you can state"],["the retry stays","newest, ties by id"]]},"alt":"Figure: Two kinds of duplicate, and what each tool does with them. A two by two grid, rows Identical rows and Same key only, columns SELECT DISTINCT and row_number = 1: Identical rows and SELECT DISTINCT: Collapsed; Identical rows and row_number = 1: One kept; Same key only and SELECT DISTINCT: Both survive; Same key only and row_number = 1: One kept. DISTINCT keeps any row that differs in any column, so a retry that differs by one timestamp survives while two genuinely separate payments that happen to match are merged. It is wrong in both directions at once. A ranking with a stated ordering is a decision you can explain."}},"blocks":[{"t":"h2","id":"three-kinds-of-duplicate","runs":[{"text":"Three kinds of duplicate"}]},{"t":"p","runs":[{"text":"The earlier cleaning lesson made the point that whether two rows are “the same” is a business question. This lesson is about the mechanics once the question is answered, and there are three different mechanics because there are three different kinds of duplicate."}]},{"t":"p","runs":[{"text":"Exact duplicates","b":true},{"text":" are rows identical in every column. They come from a file pasted twice or a job that ran twice. "},{"text":"Key duplicates","b":true},{"text":" share the identifying columns and differ elsewhere: the same payment reference with two timestamps, because a retry recorded a second row. "},{"text":"Near duplicates","b":true},{"text":" share nothing exactly and are still the same thing: "},{"text":"ada@example.com","code":true},{"text":" and "},{"text":"Ada@Example.com","code":true},{"text":", or "},{"text":"+91 98765 43210","code":true},{"text":" and "},{"text":"9876543210","code":true},{"text":"."}]},{"t":"h2","id":"finding-exact-and-key-duplicates","runs":[{"text":"Finding exact and key duplicates"}]},{"t":"code","lang":"sql","label":"SQL","text":"-- exact: group by everything\nSELECT reference, paid_on, amount, city, count(*)\nFROM raw_payments\nGROUP BY reference, paid_on, amount, city\nHAVING count(*) > 1;\n\n-- by key: group by what should be unique\nSELECT reference, count(*), min(loaded_at), max(loaded_at)\nFROM raw_payments\nGROUP BY reference\nHAVING count(*) > 1;"},{"t":"p","runs":[{"text":"The second query’s "},{"text":"min","code":true},{"text":" and "},{"text":"max","code":true},{"text":" of the load time tell you whether the duplicates came in one file or across two, which is usually the whole diagnosis. Look at ten of them before deciding anything."}]},{"t":"h2","id":"keeping-one-by-a-rule-you-can-state","runs":[{"text":"Keeping one, by a rule you can state"}]},{"t":"p","runs":[{"text":"The rule is nearly always “the latest” or “the first”, and a window function applies it:"}]},{"t":"code","lang":"sql","label":"SQL","text":"CREATE VIEW payments_deduped AS\nSELECT reference, paid_on, amount, city\nFROM (\n  SELECT *,\n         row_number() OVER (PARTITION BY reference\n                            ORDER BY loaded_at DESC, ctid DESC) AS rn\n  FROM raw_payments\n) t\nWHERE rn = 1;"},{"t":"p","runs":[{"text":"Within each reference, rows are numbered from the most recently loaded, and only number 1 survives. The "},{"text":"ctid","code":true},{"text":" tiebreaker (the physical row address in Postgres; use a serial id elsewhere) makes the choice deterministic when two rows share a load time, which the ranking lesson explained is not optional. "},{"text":"DISTINCT","code":true},{"text":" cannot do this job, because it keeps rows that differ in any column and has no notion of “latest”."}]},{"t":"p","runs":[{"text":"The rows that did not survive are "},{"text":"WHERE rn > 1","code":true},{"text":", and a count of them, by load file, goes into the load log. A deduplication that cannot say how many rows it removed is a filter, not a decision."}]},{"t":"h2","id":"deleting-in-place","runs":[{"text":"Deleting in place"}]},{"t":"p","runs":[{"text":"If the duplicates are in a real table rather than a raw staging table, and the decision is to remove them permanently:"}]},{"t":"code","lang":"sql","label":"SQL","text":"DELETE FROM payments\nWHERE id IN (\n  SELECT id FROM (\n    SELECT id, row_number() OVER (PARTITION BY reference ORDER BY created_at DESC, id DESC) AS rn\n    FROM payments\n  ) t WHERE rn > 1\n);"},{"t":"p","runs":[{"text":"Run the inner SELECT first and count it. Then run the DELETE inside a transaction, compare the row count it reports with that count, and only then commit. The module on changing data safely has the full discipline; the short version is that a DELETE without a rehearsal is how a year of payments disappears."}]},{"t":"h2","id":"near-duplicates","runs":[{"text":"Near duplicates"}]},{"t":"p","runs":[{"text":"Normalising the key, as the previous lesson showed, turns most near duplicates into exact ones: lower-case the email, strip the phone to digits, and the "},{"text":"GROUP BY","code":true},{"text":" finds them. What remains is fuzzy: "},{"text":"Priya Sharma","code":true},{"text":" and "},{"text":"Priya Sharmaa","code":true},{"text":", "},{"text":"Bombay Traders","code":true},{"text":" and "},{"text":"Bombay Trading Co","code":true},{"text":". Postgres’s "},{"text":"pg_trgm","code":true},{"text":" extension gives "},{"text":"similarity(a, b)","code":true},{"text":", a score from 0 to 1 based on shared three-letter fragments, and "},{"text":"levenshtein(a, b)","code":true},{"text":" from "},{"text":"fuzzystrmatch","code":true},{"text":" counts the edits between them."}]},{"t":"code","lang":"sql","label":"SQL","text":"SELECT a.name, b.name, similarity(a.name, b.name)\nFROM customers a JOIN customers b ON a.id < b.id\nWHERE similarity(a.name, b.name) > 0.6;"},{"t":"p","runs":[{"text":"This is a self-join over every pair, which is a million comparisons for a thousand customers; on a hundred thousand it needs the extension’s index or a blocking step that only compares names sharing a first letter and a city. And the threshold is a judgement with a cost on both sides. Set it high and "},{"text":"Sharma","code":true},{"text":" and "},{"text":"Sharmaa","code":true},{"text":" stay apart; set it low and two different people called Priya Sharma are merged, which loses a customer’s history into somebody else’s and is far harder to undo than a missed match. Review a sample of the proposed merges by hand, log every merge with both original ids, and merge fewer rather than more."}]},{"t":"h2","id":"the-habit","runs":[{"text":"The habit"}]},{"t":"p","runs":[{"text":"Say the rule out loud before the query: “one row per reference, the most recently loaded one wins, ties broken by id”. If you cannot say the rule, you are not deduplicating; you are guessing with a window function."}]}],"check":{"question":[{"text":"A team removes duplicate payments with "},{"text":"SELECT DISTINCT * FROM raw_payments","code":true},{"text":". Retried payments, which share a reference but differ by a few seconds in "},{"text":"paid_at","code":true},{"text":", all survive. Why, and what should replace it?"}],"options":[{"text":[{"text":"DISTINCT compares only the first column, so it keeps rows whose references match but whose later columns differ; adding "},{"text":"reference","code":true},{"text":" to an ORDER BY clause before the DISTINCT fixes it."}],"correct":false,"tempting":[{"text":"DISTINCT compares every selected column, not the first; ORDER BY changes presentation order, not which rows are kept."}]},{"text":[{"text":"DISTINCT keeps every row that differs in any column, and the retries differ in "},{"text":"paid_at","code":true},{"text":"; a "},{"text":"row_number()","code":true},{"text":" partitioned by reference and filtered to 1 keeps one per reference."}],"correct":true,"tempting":null},{"text":[{"text":"DISTINCT works but the retries were loaded from a second file, and DISTINCT only removes duplicates within a single load batch."}],"correct":false,"tempting":[{"text":"DISTINCT has no notion of load batches; it compares the rows it is given regardless of origin."}]},{"text":[{"text":"The "},{"text":"paid_at","code":true},{"text":" column is a timestamptz, and DISTINCT treats timestamps that differ in time zone display as unequal even when they are the same instant."}],"correct":false,"tempting":[{"text":"timestamptz equality compares instants, and in any case the retries are genuinely a few seconds apart, not the same instant."}]}],"order":[0,2,3,1],"why":null},"prev":{"slug":"strings-trim-case-and-unicode","title":"Text that looks identical and is not","minutes":9},"next":{"slug":"validating-a-cleaned-table","title":"Assertions: queries that return no rows when everything is right","minutes":8}}}