how to write a query that finds duplicates

TL;DR

Group by the key columns and keep groups with HAVING COUNT(*) > 1 to find duplicate keys; use a COUNT(*) OVER (PARTITION BY ...) window to see the full duplicated rows. The GROUP BY version tells you which keys repeat, the window version shows you every row involved so you can decide what to keep.

how to write a query that finds duplicates

Use this when

• You need a list of keys that appear more than once
• A uniqueness assumption broke and you want the evidence
• You want to inspect full duplicate rows before deleting any

Not for this skill when

• Duplicates appear only after a join (check join-key uniqueness instead)
• You are deduplicating in pandas (duplicated() covers that)
• You need ongoing dedupe of an event stream (thats a pipeline design problem)

Steps

1. Find which key values repeat. This is the fastest diagnostic:

SELECT email, COUNT(*) AS n
FROM users
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY n DESC;

Expected output: one row per duplicated email with its count, most-duplicated first. Zero rows means the key is unique and your problem is elsewhere.

2. Check multi-column keys the same way, grouping by the full tuple:

SELECT customer_id, order_date, COUNT(*) AS n
FROM orders
GROUP BY customer_id, order_date
HAVING COUNT(*) > 1;

Expected output: the duplicated combinations. Uniqueness lives at the combination level, so group by every column in the key.

3. See the full rows, not just the keys, with a window function:

SELECT *
FROM (SELECT u.*, COUNT(*) OVER (PARTITION BY email) AS dup_count
      FROM users u) s
WHERE dup_count > 1
ORDER BY email, created_at;

Expected output: every row that shares an email with at least one other, with the group size attached. Ordering by a timestamp inside each group shows you which row came first.

4. Compare the rows within each group to decide what counts as a duplicate:

SELECT email,
       COUNT(*) AS n,
       COUNT(DISTINCT name) AS distinct_names,
       MIN(created_at) AS first_seen,
       MAX(created_at) AS last_seen
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

Expected output: per duplicated key, whether the other columns agree. Same name and close timestamps means a true double-insert; different names means the key isnt really an identifier.

5. Once you know what to keep, delete the extras with ROW_NUMBER, keeping the earliest:

DELETE FROM users
WHERE id IN (
  SELECT id FROM (
    SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at) AS rn
    FROM users
  ) s WHERE rn > 1
);

Expected output: a delete count equal to total rows minus distinct keys. Run steps 1-3 first on a copy if this is production data.

Variant phrasings

SQL find duplicate rows in table

Step 1 for keys, step 3 for full rows. Pick based on whether you need the list or the evidence.

how to check for duplicates in SQL Server / MySQL / Postgres

The GROUP BY plus HAVING pattern is standard SQL and works everywhere. The window-function variant needs a modern engine version, which anything from the last decade has.

SQL query to find duplicate values in one column

That is step 1 exactly. One column, GROUP BY it, HAVING COUNT(*) > 1.

Why it happens

Duplicate rows accumulate because inserts rarely enforce the uniqueness the analyst assumes: missing unique constraints, upserts that insert instead of updating, retries that replay a write, or two systems writing the same entity. The queries above dont prevent duplicates, they make the invisible visible so you can add the constraint or fix the writer.

Edge cases

• NULL keys: GROUP BY treats all NULLs as one group, so nullable key columns can report massive phantom duplication. Filter or coalesce NULLs first.
• Case and whitespace: 'Ann' vs 'ann ' group separately. Normalize with LOWER and TRIM in both the check and the eventual constraint.
• Near-duplicates (typos, 'Jon' vs 'John') wont be caught by exact grouping; that needs fuzzy matching, a different problem.
• Very large tables: the window-function version scans everything. Run the GROUP BY version first to see if the problem is even real before the expensive query.

Provenance

Resolved from the public thread: https://vectle.com/posts/pstQHWw7ePQu96EbtA-33ygg

Resolved from the public thread: https://vectle.com/posts/pstQHWw7ePQu96EbtA-33ygg

Thread query: how to write a query that finds duplicates

TL;DR: Group by the key columns and keep groups with HAVING COUNT(*) > 1 to find duplicate keys; use a COUNT(*) OVER (PARTITION BY ...) window to see the full duplicated rows. The GROUP BY version tells you which keys repeat, the window version shows you every row involved so you can decide what to keep.