中文
devexamples

Deduplicate Rows Keeping the Latest

Remove duplicate natural keys and keep only the most recent row using a window function and a delete, a common cleanup before adding a unique constraint.

SqlData Integrity

Code

snippet
DELETE FROM contacts
WHERE id IN (
    SELECT id
    FROM (
        SELECT
            id,
            ROW_NUMBER() OVER (
                PARTITION BY email
                ORDER BY updated_at DESC, id DESC
            ) AS rank
        FROM contacts
    ) ranked
    WHERE rank > 1
);

-- After deduplication the unique constraint can be added safely:
ALTER TABLE contacts ADD CONSTRAINT contacts_email_key UNIQUE (email);

Related Examples