Deduplicate records with ROW_NUMBER() in a Query Activity

Lead imports often bring the same email several times. If the target Data Extension uses email as its primary key, the Query Activity fails with a primary key violation. If it does not, you send the same email twice to the same person. The fix is to keep one record per email, usually the most recent one.

The snippet

SELECT
    t.Email,
    t.Name,
    t.Source,
    t.SignupDate
FROM (
    SELECT
        l.Email,
        l.Name,
        l.Source,
        l.SignupDate,
        ROW_NUMBER() OVER (
            PARTITION BY LOWER(LTRIM(RTRIM(l.Email)))
            ORDER BY l.SignupDate DESC
        ) AS rn
    FROM Imported_Leads l
    WHERE l.Email IS NOT NULL
      AND l.Email <> ''
) t
WHERE t.rn = 1

How it works

• ROW_NUMBER() numbers the rows within each group, starting at 1.
• PARTITION BY defines the group: here, each email. LOWER(LTRIM(RTRIM(...))) makes "Ana@X.com " and "ana@x.com" fall into the same group.
• ORDER BY SignupDate DESC makes the most recent record number 1.
• The outer WHERE t.rn = 1 keeps only that record.

The numbering has to live in a subquery: the WHERE of the same query cannot see the rn column.

Choosing the "best" record

The ORDER BY decides who wins a tie. A few common rules:

In the Query Activity

1. A target Data Extension with Email as the primary key.
2. The Overwrite action for a clean audience on every run, or Update to accumulate over time (the newest version of each email replaces the previous one).
3. If the source Data Extension is large, filter by date inside the subquery so you stay within the Query Activity's 30-minute limit.