How to Find Duplicate Rows in SQL (and Decide Which One to Keep)

To find duplicate rows in SQL, group by the columns that should be unique and keep the groups where COUNT(*) > 1. That query tells you which keys repeat. It does not tell you which copy to keep, and that second decision is where many deduplication bugs come from. This guide walks through both halves in DuckDB SQL: finding exact and key-based duplicates, listing every copy for review, and keeping one survivor per key with a rule you can explain to someone else.

Key Takeaways

• GROUP BY key HAVING COUNT(*) > 1 finds repeated keys; a window COUNT(*) OVER (PARTITION BY key) shows every row in each duplicate group.
• Decide the grain first: an exact duplicate (every column equal) and a key duplicate (same customer, different details) need different handling.
• GROUP BY puts all NULL keys in one group, so rows with a missing key look like duplicates of each other. Filter or handle them on purpose.
• Keep one row per key with ROW_NUMBER() OVER (PARTITION BY key ORDER BY ...) and always end the ORDER BY with a unique tie-breaker so the result is repeatable.
• Check the result: rows out should equal distinct keys, and the removed rows should be saved somewhere a reviewer can see them.

The example table

Every query below runs against this small customer table. It has the problems real exports have: an exact copy (rows 1 and 2), the same customer with different details (rows 3 and 4), an email that differs only by case and a leading space (rows 5 and 6), and two rows with no email at all (rows 7 and 8).

CREATE TABLE customers AS
SELECT * FROM (VALUES
  (1, 'ana@example.com',   'Ana Silva',  'Lisbon', TIMESTAMP '2026-01-10 09:00'),
  (2, 'ana@example.com',   'Ana Silva',  'Lisbon', TIMESTAMP '2026-01-10 09:00'),
  (3, 'ben@example.com',   'Ben Okafor', 'Lagos',  TIMESTAMP '2026-02-01 12:00'),
  (4, 'ben@example.com',   'Ben Okafor', NULL,     TIMESTAMP '2026-03-15 08:30'),
  (5, ' Cara@Example.com', 'Cara Diaz',  'Madrid', TIMESTAMP '2026-01-20 10:00'),
  (6, 'cara@example.com',  'Cara Díaz',  'Madrid', TIMESTAMP '2026-04-02 16:45'),
  (7, NULL,                'Dev Patel',  'Pune',   TIMESTAMP '2026-02-11 11:00'),
  (8, NULL,                'Eve Novak',  'Brno',   TIMESTAMP '2026-02-12 11:00')
) AS t(customer_id, email, full_name, city, updated_at);

The outputs shown in this article come from running these statements in DuckDB 1.5.1 (the column-type row is omitted for space).

Step 1: Decide what "duplicate" means for this table

A duplicate is only defined relative to a grain: the set of columns that should identify one real-world thing. For a customer list the grain might be the email address. For order lines it might be order number plus line number. Before writing any query, write the grain down in one sentence, because every later choice depends on it. The lesson on finding the row grain before removing duplicates covers how to test a candidate grain against the data.

There are two kinds of duplicate worth separating:

Some repeats are not errors at all. Two identical order lines can be two real purchases of the same item. The lesson on legitimate repeats explains how to tell the difference before you delete anything.

Step 2: Find exact duplicate rows

Group by every column that carries meaning and count the rows in each group. Leave out the surrogate id (customer_id here), because it is unique by construction and would hide the copies.

SELECT email, full_name, city, updated_at, COUNT(*) AS copies
FROM customers
GROUP BY email, full_name, city, updated_at
HAVING COUNT(*) > 1;

┌─────────────────┬───────────┬─────────┬─────────────────────┬────────┐
│      email      │ full_name │  city   │     updated_at      │ copies │
├─────────────────┼───────────┼─────────┼─────────────────────┼────────┤
│ ana@example.com │ Ana Silva │ Lisbon  │ 2026-01-10 09:00:00 │      2 │
└─────────────────┴───────────┴─────────┴─────────────────────┴────────┘

HAVING filters groups after aggregation, which is why the count condition goes there rather than in WHERE. On a wide table, typing every column is tedious. DuckDB's COLUMNS(* EXCLUDE (...)) expression (DuckDB star expression documentation) and GROUP BY ALL (DuckDB GROUP BY documentation) do the same job without the list:

SELECT COLUMNS(* EXCLUDE (customer_id)), COUNT(*) AS copies
FROM customers
GROUP BY ALL
HAVING COUNT(*) > 1;

It returns the same single Ana Silva group.

Step 3: Find duplicates on a business key

Most real duplicates are key duplicates. Group by the key alone:

SELECT email, COUNT(*) AS rows_per_email
FROM customers
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY email;

┌─────────────────┬────────────────┐
│      email      │ rows_per_email │
├─────────────────┼────────────────┤
│ ana@example.com │              2 │
│ ben@example.com │              2 │
│ NULL            │              2 │
└─────────────────┴────────────────┘

Two problems show up in that output.

NULL keys form their own group

GROUP BY puts all NULL keys into one group, as the output above shows, so Dev Patel and Eve Novak appear as a "duplicate" of each other even though they are different people who simply have no email. A missing key is not a match. Exclude those rows from the duplicate check and handle them separately, for example by sending them to a review list:

SELECT email, COUNT(*) AS rows_per_email
FROM customers
WHERE email IS NOT NULL
GROUP BY email
HAVING COUNT(*) > 1;

The same trap catches joins: the free exercise Join orders to customers without matching blanks practises it.

Formatting differences hide duplicates

Cara Diaz is missing from the result because ' Cara@Example.com' and 'cara@example.com' are different strings. Group by a normalized key instead:

SELECT lower(trim(email)) AS email_key, COUNT(*) AS rows_per_email
FROM customers
WHERE email IS NOT NULL
GROUP BY email_key
HAVING COUNT(*) > 1
ORDER BY email_key;

┌──────────────────┬────────────────┐
│    email_key     │ rows_per_email │
├──────────────────┼────────────────┤
│ ana@example.com  │              2 │
│ ben@example.com  │              2 │
│ cara@example.com │              2 │
└──────────────────┴────────────────┘

Normalization is a policy, not a reflex. Lowercasing an email is usually safe; stripping dots or plus-tags is not safe for every provider. The free exercise Normalize emails under a declared policy shows where to draw that line.

Step 4: List every row in each duplicate group

A grouped count tells you that ben@example.com appears twice. To decide which Ben to keep, you need to see both rows side by side. A window count attaches the group size to every row without collapsing them (DuckDB window functions documentation):

SELECT *
FROM (
  SELECT *,
         lower(trim(email)) AS email_key,
         COUNT(*) OVER (PARTITION BY lower(trim(email))) AS group_size
  FROM customers
  WHERE email IS NOT NULL
)
WHERE group_size > 1
ORDER BY email_key, updated_at, customer_id;

┌─────────────┬───────────────────┬────────────┬─────────┬─────────────────────┬──────────────────┬────────────┐
│ customer_id │       email       │ full_name  │  city   │     updated_at      │    email_key     │ group_size │
├─────────────┼───────────────────┼────────────┼─────────┼─────────────────────┼──────────────────┼────────────┤
│           1 │ ana@example.com   │ Ana Silva  │ Lisbon  │ 2026-01-10 09:00:00 │ ana@example.com  │          2 │
│           2 │ ana@example.com   │ Ana Silva  │ Lisbon  │ 2026-01-10 09:00:00 │ ana@example.com  │          2 │
│           3 │ ben@example.com   │ Ben Okafor │ Lagos   │ 2026-02-01 12:00:00 │ ben@example.com  │          2 │
│           4 │ ben@example.com   │ Ben Okafor │ NULL    │ 2026-03-15 08:30:00 │ ben@example.com  │          2 │
│           5 │  Cara@Example.com │ Cara Diaz  │ Madrid  │ 2026-01-20 10:00:00 │ cara@example.com │          2 │
│           6 │ cara@example.com  │ Cara Díaz  │ Madrid  │ 2026-04-02 16:45:00 │ cara@example.com │          2 │
└─────────────┴───────────────────┴────────────┴─────────┴─────────────────────┴──────────────────┴────────────┘

This is the view to read before choosing a rule. Ben's newer row has lost his city. Cara's newer row has the accented spelling of her name. A rule that blindly keeps the latest row would throw away a known city for Ben.

Step 5: Decide which row to keep

A survivor is the one row you keep from each group of duplicates. The survivor rule is a business decision, so write it down before you write the SQL. The common options:

Whatever you choose, the rule must be deterministic: run it twice on the same data and get the same survivor. Ana's two rows tie on every column, including updated_at, so "latest" alone cannot choose between them. End every ordering with a unique column such as the id. The lesson on deterministic survivor rules explains why a missing tie-breaker produces results that change between runs.

Step 6: Keep one row per key with ROW_NUMBER

ROW_NUMBER() numbers the rows inside each partition in the order you give it. The survivor is row 1.

SELECT customer_id, email_key, full_name, city, updated_at
FROM (
  SELECT *,
         lower(trim(email)) AS email_key,
         ROW_NUMBER() OVER (
           PARTITION BY lower(trim(email))
           ORDER BY updated_at DESC, customer_id DESC
         ) AS rn
  FROM customers
  WHERE email IS NOT NULL
)
WHERE rn = 1
ORDER BY email_key;

┌─────────────┬──────────────────┬────────────┬─────────┬─────────────────────┐
│ customer_id │    email_key     │ full_name  │  city   │     updated_at      │
├─────────────┼──────────────────┼────────────┼─────────┼─────────────────────┤
│           2 │ ana@example.com  │ Ana Silva  │ Lisbon  │ 2026-01-10 09:00:00 │
│           4 │ ben@example.com  │ Ben Okafor │ NULL    │ 2026-03-15 08:30:00 │
│           6 │ cara@example.com │ Cara Díaz  │ Madrid  │ 2026-04-02 16:45:00 │
└─────────────┴──────────────────┴────────────┴─────────┴─────────────────────┘

DuckDB also supports QUALIFY, which filters on a window function directly and removes the subquery (DuckDB QUALIFY documentation). This returns the same three rows:

SELECT customer_id, lower(trim(email)) AS email_key, full_name, city, updated_at
FROM customers
WHERE email IS NOT NULL
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY lower(trim(email))
  ORDER BY updated_at DESC, customer_id DESC
) = 1;

To prefer complete rows, put the completeness test first in the ordering. (city IS NULL) is false for rows with a city, and false sorts before true:

SELECT customer_id, lower(trim(email)) AS email_key, city, updated_at
FROM customers
WHERE email IS NOT NULL
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY lower(trim(email))
  ORDER BY (city IS NULL), updated_at DESC, customer_id DESC
) = 1
ORDER BY email_key;

┌─────────────┬──────────────────┬─────────┬─────────────────────┐
│ customer_id │    email_key     │  city   │     updated_at      │
├─────────────┼──────────────────┼─────────┼─────────────────────┤
│           2 │ ana@example.com  │ Lisbon  │ 2026-01-10 09:00:00 │
│           3 │ ben@example.com  │ Lagos   │ 2026-02-01 12:00:00 │
│           6 │ cara@example.com │ Madrid  │ 2026-04-02 16:45:00 │
└─────────────┴──────────────────┴─────────┴─────────────────────┘

Ben's survivor is now row 3, the one with a city. Same data, different rule, different answer, which is why the rule belongs in writing.

Other ways to keep one row

• SELECT DISTINCT removes exact duplicates only. It cannot pick between rows that differ in any column, and if you include a surrogate id every row is distinct, so nothing is removed.
• DISTINCT ON (key) keeps the first row per key according to the ORDER BY (DuckDB SELECT documentation). It works in DuckDB and PostgreSQL but not in every database. PostgreSQL requires the ORDER BY to start with the DISTINCT ON expressions (PostgreSQL SELECT documentation); DuckDB does not, but without an ORDER BY the row kept is arbitrary.
• GROUP BY key with MAX() on each column looks tempting, but it builds a new row from different source rows: the latest date from one row and the alphabetically largest city from another. Use it only when that field-by-field merge is what you actually want.

The remove duplicates operation reference shows the same survivor pattern side by side in SQL, pandas and the visual builder.

Step 7: Check the result and keep the losers

Deduplication should leave an audit trail. Two checks catch many mistakes. First, the number of rows out should equal the number of distinct keys:

WITH deduped AS (
  SELECT * FROM customers
  WHERE email IS NOT NULL
  QUALIFY ROW_NUMBER() OVER (
    PARTITION BY lower(trim(email)) ORDER BY updated_at DESC, customer_id DESC) = 1
)
SELECT
  (SELECT COUNT(*) FROM customers WHERE email IS NOT NULL)   AS rows_in,
  (SELECT COUNT(DISTINCT lower(trim(email))) FROM customers) AS distinct_keys,
  (SELECT COUNT(*) FROM deduped)                             AS rows_out;

┌─────────┬───────────────┬──────────┐
│ rows_in │ distinct_keys │ rows_out │
├─────────┼───────────────┼──────────┤
│       6 │             3 │        3 │
└─────────┴───────────────┴──────────┘

Second, save the rows you removed, with the id of the row that replaced each one. A named WINDOW clause lets the survivor id and the row number share one definition:

SELECT customer_id, lower(trim(email)) AS email_key, updated_at,
       FIRST_VALUE(customer_id) OVER w AS kept_customer_id
FROM customers
WHERE email IS NOT NULL
WINDOW w AS (PARTITION BY lower(trim(email)) ORDER BY updated_at DESC, customer_id DESC)
QUALIFY ROW_NUMBER() OVER w > 1
ORDER BY email_key;

┌─────────────┬──────────────────┬─────────────────────┬──────────────────┐
│ customer_id │    email_key     │     updated_at      │ kept_customer_id │
├─────────────┼──────────────────┼─────────────────────┼──────────────────┤
│           1 │ ana@example.com  │ 2026-01-10 09:00:00 │                2 │
│           3 │ ben@example.com  │ 2026-02-01 12:00:00 │                4 │
│           5 │ cara@example.com │ 2026-01-20 10:00:00 │                6 │
└─────────────┴──────────────────┴─────────────────────┴──────────────────┘

That table is what a colleague needs to check your work, and what you need when someone asks where customer 3 went. The guide on quarantining rows instead of deleting them covers where such rows should live.

Practise finding and resolving duplicates

Reading the queries is the easy part; choosing the rule on unfamiliar data is the skill. These exercises have graded answers:

• Pick one surviving record per account (free): apply a written survivor rule with a tie-breaker.
• Deduplicate order lines at the right grain: find the grain before removing anything.
• Build a duplicate review queue: list duplicate groups for a person to resolve.

For a broader view that includes fuzzy matching across sources, read deduplication and entity resolution without false merges.

Frequently Asked Questions

How do I find duplicates across multiple columns in SQL?

List all the columns in the GROUP BY and keep groups with HAVING COUNT(*) > 1. Each group is one combination of values that appears more than once. In DuckDB, GROUP BY ALL groups by every non-aggregated column in the SELECT, which saves typing on wide tables.

How do I delete duplicate rows but keep one?

Build the survivors and write them to a new table rather than deleting in place:

CREATE TABLE customers_clean AS
SELECT * FROM customers
QUALIFY email IS NULL
     OR ROW_NUMBER() OVER (
          PARTITION BY lower(trim(email)) ORDER BY updated_at DESC, customer_id DESC) = 1;

The email IS NULL condition keeps every row that has no key, so Dev and Eve stay in the clean table (five rows: 2, 4, 6, 7 and 8) instead of disappearing. A clean table keeps the original for comparison, and the losers query from Step 7 gives you a record of what was removed.

Why does my duplicate query return a NULL group?

GROUP BY places every NULL key in a single group, so rows with a missing key are counted together. Add WHERE key IS NOT NULL to the duplicate check and decide separately what to do with rows that have no key.

Is SELECT DISTINCT enough to remove duplicates?

Only for exact duplicates where every selected column matches. If two rows share a key but differ anywhere else, DISTINCT keeps both. Key duplicates need a survivor rule.

Related reading

• QUALIFY in DuckDB: The Cleanest Way to Keep the Latest Row
• pandas drop_duplicates: keep, subset and the Mistakes That Lose Data
• How to Merge Two Customer Lists Without Creating Duplicates

Sources

All queries were run against the example table in DuckDB 1.5.1; behaviour notes are checked against the official documentation below. Spotted an error? Tell us through the contact page linked in the site footer and it will be corrected.

• DuckDB documentation: GROUP BY clause, retrieved 2026-11-05.
• DuckDB documentation: Star expression and COLUMNS, retrieved 2026-11-05.
• DuckDB documentation: QUALIFY clause, retrieved 2026-11-05.
• DuckDB documentation: Window functions, retrieved 2026-11-05.
• DuckDB documentation: SELECT clause, including DISTINCT ON, retrieved 2026-11-05.
• PostgreSQL documentation: SELECT, retrieved 2026-11-05.