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.

1 Answer 1

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..

5
  • 2
    Quickdraw McGraw over here. what was it, 6 minutes after being posted? Good job on a great answer though! Commented Dec 12, 2019 at 14:12
  • I need the primary key field as well Commented 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).. Commented Dec 12, 2019 at 14:34
  • Yes, I did that. It works fine. Thanks a lot!!! You have saved me today :) Commented 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.. Commented 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 .