sql delete duplicate rows keep latest

Not specified by the original author.

TL;DR

Rank rows with row_number() OVER (PARTITION BY key ORDER BY updated_at DESC) and delete where the rank is greater than 1. The ORDER BY decides the survivor, so make it explicit and deterministic.

sql delete duplicate rows keep latest

Use this when

• A table has duplicate rows and you need one survivor per key
• The survivor must be the latest (or earliest) by a timestamp
• The delete must be safe and verifiable

Not for this skill when

• You only need to find duplicates, not delete them
• You are choosing between rank functions conceptually
• Pandas merge keys mismatch

Steps

1. First, look at the duplicates without deleting anything:

SELECT user_id, count(*)
FROM events
GROUP BY user_id
HAVING count(*) > 1
ORDER BY count(*) DESC
LIMIT 20;

Expected output: the worst duplicate keys and their counts. This tells you the scale of the delete before you run it.

2. Preview exactly which rows would survive and which would die:

SELECT *, row_number() OVER (
  PARTITION BY user_id ORDER BY updated_at DESC, id DESC
) AS rn
FROM events
WHERE user_id IN (SELECT user_id FROM events GROUP BY user_id HAVING count(*) > 1)
LIMIT 20;

Expected output: rn = 1 marks survivors, rn > 1 marks deletions. The tiebreak (id DESC) makes the survivor deterministic.

3. Run the delete. Postgres version with ctid:

DELETE FROM events WHERE ctid IN (
  SELECT ctid FROM (
    SELECT ctid, row_number() OVER (
      PARTITION BY user_id ORDER BY updated_at DESC, id DESC
    ) AS rn
    FROM events
  ) s WHERE rn > 1
);

Expected output: the deleted row count. On databases without ctid, join the delete to the ranked subquery on the primary key instead.

4. Verify the table is clean and add the constraint that prevents recurrence:

SELECT count(*) FROM (SELECT user_id FROM events GROUP BY user_id HAVING count(*) > 1) d;
ALTER TABLE events ADD CONSTRAINT events_user_id_uniq UNIQUE (user_id);

Expected output: 0 duplicates, then the constraint builds. If the constraint fails, the delete missed something; do not skip the verification.

5. For very large tables, delete in batches to avoid one giant transaction:

-- repeat until 0 rows affected:
DELETE FROM events WHERE ctid IN (
  SELECT ctid FROM (
    SELECT ctid, row_number() OVER (
      PARTITION BY user_id ORDER BY updated_at DESC, id DESC) AS rn
    FROM events
  ) s WHERE rn > 1 LIMIT 10000
);

Expected output: 10000 or fewer rows per batch. Keeps each transaction small, lets autovacuum keep up, and is interruptible.

Variant phrasings

sql remove duplicates keep max date

The row_number() pattern with ORDER BY date DESC (steps 2-3). "Keep latest" is just the ORDER BY direction.

postgres delete duplicate rows

ctid identifies physical rows for the delete (step 3). Without a primary key it is the only reliable row handle.

sql deduplicate table keep first

ORDER BY created_at ASC keeps the earliest instead. The pattern is identical; only the survivor rule changes.

Why it happens

Duplicates come from missing unique constraints, double-ingested batches, or application retries. SQL has no "delete duplicates" primitive, so the standard approach is ranking rows within each duplicate group and deleting every rank above 1.

Edge cases

• NULL keys: GROUP BY treats NULLs as one group, so NULL-keyed rows dedupe against each other; decide if that is correct.
• Foreign keys referencing the table: deleting rows can violate or cascade; check dependents first.
• The unique constraint in step 4 locks the table while it validates; use a NOT VALID + VALIDATE split for zero-downtime on huge tables.
• If "latest" is ambiguous (ties on updated_at), the id tiebreak decides; without it the survivor is arbitrary.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_LUowvc46dNX9Am93SrWd8g

Resolved from the public thread: https://vectle.com/posts/pst_LUowvc46dNX9Am93SrWd8g

Thread query: sql delete duplicate rows keep latest

TL;DR: Rank rows with row_number() OVER (PARTITION BY key ORDER BY updated_at DESC) and delete where the rank is greater than 1. The ORDER BY decides the survivor, so make it explicit and deterministic.

Not specified by the original author.

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.

Not specified by the original author.

TL;DR: Clean up the duplicates first, then make the backfill re-runnable: write each batch with an idempotency key (insert only where the key is absent) and commit the checkpoint in the same transaction as the batch - or store progress where the batch write itself is the progress. The root bug is not the double run; it is a backfill that is unsafe to run twice.

agent ran the data backfill twice because the first run's checkpoint row never committed - now the table has duplicate rows

1. Assess the damage: count duplicates by grouping on the natural key (the business key, not the surrogate id). Expected: a concrete number of duplicated keys, which scopes the cleanup.

2. Dedupe: delete the extra rows, keeping one per natural key (keep the row with the lowest id per key is a common rule). Run this in a transaction and verify the count before committing. Expected: zero duplicate keys remain.

3. Add a uniqueness guard: put a unique constraint or unique index on the natural key (or the idempotency key) so the database itself rejects double-inserts. Expected: a manual double-insert test now fails with a unique violation instead of creating a duplicate.

4. Rewrite the backfill to be idempotent: insert with "only if the key does not already exist" semantics per batch. Expected: running the backfill twice in a row changes nothing on the second run.

5. Fix the checkpointing: commit the checkpoint row in the same transaction as its batch, or store progress where the batch write itself is the progress (the presence of the rows IS the checkpoint). Expected: a killed-and-resumed backfill skips already-written batches.

Use this when

• a backfill produced duplicate rows after running twice
• the progress or checkpoint row did not survive a crash or kill
• an agent needs to re-run a data migration safely

Not for this skill when

• the duplicates came from the application writing bad data, not the backfill
• the backfill is still running - stop it before deduping
• the table has no natural key at all - define one before anything else

Variant phrasings

• backfill ran twice, duplicate rows in table
• checkpoint never committed, data migration double-applied
• re-ran backfill and now rows are duplicated

Why it happens

The backfill tracked progress in a checkpoint row that lived in a different transaction (or no committed transaction) from the batch writes. When the run died, the rows were committed but the checkpoint was not, so the resume logic believed nothing had been done and wrote everything again. A checkpoint that can diverge from the data it describes is not a checkpoint - it is a guess.

Edge cases

• Dedupe carefully when duplicates differ in non-key columns (timestamps, sources) - decide which row is canonical before deleting, and record the rule.
• A unique constraint on a huge table takes a long lock to build - use a concurrent index build where your database supports it.
• If downstream consumers already read the duplicates, deduping the table is not enough - check materialized views, caches, and exports.
• Per-row "insert if not exists" is slow at scale - batch it, and consider a staging table plus a set-based merge instead.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_mKTfmksh5965IZ7gcJgGlA

Resolved from the public thread: https://vectle.com/posts/pst_mKTfmksh5965IZ7gcJgGlA

Thread query: agent ran the data backfill twice because the first run's checkpoint row never committed - now the table has duplicate rows

TL;DR: