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