Write SQL that keeps the newest row for each external import key and deletes older duplicates.

Table: ImportedOrders(ImportRowId, SourceSystem, ExternalId, ImportedAtUtc, PayloadHash). A duplicate is a repeated (SourceSystem, ExternalId). Keep the newest timestamp, breaking ties with the largest ImportRowId. First produce a preview query, then show the transactional delete.

Three rows share source ERP and external id A-42. The newest timestamp is kept. If two newest timestamps tie, the larger import row id survives.

• The retention order must be deterministic.
• Preview the exact losing ids before deletion.
• Run the delete in a transaction with an operational rollback plan.
• After cleanup, add a unique constraint or repair the importer so duplicates cannot return.

I rank rows inside each business key, newest first, and treat rows after rank one as duplicates. I preview and reconcile the exact losing ids before opening the transaction. The delete emits its affected ids, and I verify both that output and the remaining duplicate count in the same session before choosing COMMIT; otherwise I roll back. The cleanup is incomplete until the write path becomes idempotent and the database enforces the intended key where possible.

-- Preview first.
WITH RankedImports AS
(
    SELECT
        i.ImportRowId,
        i.SourceSystem,
        i.ExternalId,
        i.ImportedAtUtc,
        ROW_NUMBER() OVER
        (
            PARTITION BY i.SourceSystem, i.ExternalId
            ORDER BY i.ImportedAtUtc DESC, i.ImportRowId DESC
        ) AS rn
    FROM dbo.ImportedOrders AS i
)
SELECT ImportRowId, SourceSystem, ExternalId, ImportedAtUtc
FROM RankedImports
WHERE rn > 1
ORDER BY SourceSystem, ExternalId, rn;

-- Execute only after the preview is reviewed and backed up.
BEGIN TRANSACTION;

WITH RankedImports AS
(
    SELECT
        i.ImportRowId,
        ROW_NUMBER() OVER
        (
            PARTITION BY i.SourceSystem, i.ExternalId
            ORDER BY i.ImportedAtUtc DESC, i.ImportRowId DESC
        ) AS rn
    FROM dbo.ImportedOrders AS i
)
DELETE FROM RankedImports
OUTPUT deleted.ImportRowId
WHERE rn > 1;

-- In the same session, reconcile the OUTPUT and verify no duplicates remain.
-- COMMIT TRANSACTION;   -- run only after reconciliation succeeds
-- ROLLBACK TRANSACTION; -- use if counts or surviving rows are unexpected

The ranking sorts rows by business key and retention order. A supporting index on (SourceSystem, ExternalId, ImportedAtUtc DESC, ImportRowId DESC) reduces sort work. Large cleanup jobs should be batched to control log growth and lock duration.

• Equal import timestamps.
• A business key with many duplicates.
• Rows that share ExternalId but come from different source systems.
• New imports arriving during the cleanup window.

• Create timestamp ties to verify the id tie-breaker.
• Verify different source systems remain independent.
• Rerun the preview after deletion and expect zero duplicate rows.
• Test the unique rule or idempotent importer after cleanup.

• Deleting before previewing the exact target rows.
• Keeping an arbitrary row because the ranking has no tie-breaker.
• Partitioning only by external id and merging different sources.
• Showing an unconditional commit before the delete output and surviving rows are reconciled.
• Cleaning data without fixing the write path that created duplicates.