0

I have a data extension that has duplicate email address and it has a primary key field. I need to deduplicate the data extension based on the email address. The rows that are duplicate have same email address but different id value.

Improve this question

1 Answer 1

Reset to default
1

You can use this query to deduplicate using the email address: 

SELECT
SubscriberKey,
Email

FROM
(SELECT DEST.SubscriberKey, DEST.Email, DEST.Creation_Date,
        ROW_NUMBER() OVER (PARTITION BY DEST.Email ORDER BY DEST.Creation_Date DESC) 
        AS Row
    FROM YOUR_DE_NAME DEST 
 ) as s
 WHERE row=1

In this case, the query will return the record having the last creation date. You can add more columns to sort duplicate results..

Improve this answer
5
  • 2
    Quickdraw McGraw over here. what was it, 6 minutes after being posted? Good job on a great answer though!
    – Gortonington
    Dec 12, 2019 at 14:12
  • I need the primary key field as well
    – Swati Mishra
    Dec 12, 2019 at 14:27
  • ^^ Thanks Gregory.. @swatimishra sorry I don't get you. Although, you can replace SubscriberKey by your Primary Key, or add your primary key in both parts of the query (as email and subscriberkey)..
    – Rachid Mamai
    Dec 12, 2019 at 14:34
  • Yes, I did that. It works fine. Thanks a lot!!! You have saved me today :)
    – Swati Mishra
    Dec 12, 2019 at 15:01
  • Glad I can help. Don't forget mark it as accepted (click the checkmark icon), it will be easy for others to see it..
    – Rachid Mamai
    Dec 12, 2019 at 15:04

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .