The Problem: Duplicate Rows in Event Logs

Every production system eventually accumulates duplicate rows — retried webhook deliveries, idempotent API calls that slipped through, or a batch job that ran twice. Deleting the extras by hand is risky; you need a repeatable, auditable way to keep only the canonical record.

Why a CTE + Window Function Beats a Temp Table

Temporary tables work, but they add round‑trips, lock contention, and extra cleanup code. A single statement that uses a Common Table Expression (CTE) and a window function stays in the optimizer’s plan, runs in one transaction, and is easy to version‑control.

If you can express the rule "keep the newest row per correlation_id" in pure SQL, the engine can push predicates down and avoid materializing the whole table.

Step‑by‑Step Solution

Assume a table events with columns id (primary key), correlation_id, payload, and created_at. We want one row per correlation_id — the one with the greatest created_at.

WITH ranked AS (
    SELECT
        id,
        correlation_id,
        payload,
        created_at,
        ROW_NUMBER() OVER (
            PARTITION BY correlation_id
            ORDER BY created_at DESC
        ) AS rn
    FROM events
)
DELETE FROM events
WHERE id IN (
    SELECT id FROM ranked WHERE rn > 1
);

The CTE ranked assigns a sequential number to each row inside its correlation_id partition, ordered by newest first. Rows where rn > 1 are duplicates and get deleted.

Handling Ties Deterministically

When two rows share the exact same timestamp, ROW_NUMBER picks arbitrarily. Add a tie‑breaker — usually the primary key — to make the result stable:

ROW_NUMBER() OVER (
    PARTITION BY correlation_id
    ORDER BY created_at DESC, id DESC
) AS rn

Turning the Delete into a Select for Review

Before running the delete, preview the rows that will disappear:

SELECT id, correlation_id, created_at
FROM (
    SELECT
        id,
        correlation_id,
        created_at,
        ROW_NUMBER() OVER (
            PARTITION BY correlation_id
            ORDER BY created_at DESC, id DESC
        ) AS rn
    FROM events
) sub
WHERE rn > 1
ORDER BY correlation_id, created_at;

Run this in a transaction, verify the count, then commit the delete. The same CTE can be reused for a MERGE or an upsert if you need to keep the latest payload in a separate summary table.

Performance Tips

  • Index (correlation_id, created_at DESC, id DESC) so the window function can scan the index only.
  • If the table is huge, consider partitioning by correlation_id hash or time‑based range; the CTE still works unchanged.
  • Avoid SELECT * inside the CTE — list only the columns you need for the delete predicate.

When Not to Use This Pattern

If duplicates are the norm and you need to keep *all* versions for audit, move the logic to an insert‑only archive table instead of deleting. Also, on databases that lack window functions (very old MySQL versions), fall back to a correlated subquery or a temporary table.

Final Thoughts

I keep a snippet like this in my dotfiles because it turns a messy cleanup job into a single, reviewable statement. The next time you see a "duplicate key" error in the logs, run the preview query, confirm the rows, and let the CTE do the heavy lifting.