0

I am trying to write a query to avoid duplicate emails being sent out. I am using contact ID as subscriber key, so multiple contacts can share the same email address across the DB. I do not want to be sending multiple emails to the same address. Upon checking with SFMC they suggested this be handle at the SQL level. My existing query is quite big since I need a lot of info. I am not sure where I fit in the "SELECT DISTINCT" function in my query. Thanks in Advance

1
  • Hey, @Esha. We can better help you if you'd share some form of the query that you've already written. Commented Apr 20, 2021 at 19:02

1 Answer 1

2

SELECT DISTINCT works on the entire row, not a single field.

I like to use a partition to pick one of the duplicate rows:

select top 1 with ties
  d.contactID
, d.emailAddress
, d.insertedDate
from YOURDATAEXTENSION d
order by row_number() over (partition by d.emailAddress order by d.insertedDate desc)

The row_number() over (partition by will number rows from 1 to x for each unique combination of the proceeding fields, sorted by the order by clause. The top 1 with ties gives you all the rows that where row_number() is 1.

In your scenario, emailAddress is the column you'd like to de-duplicate by, so that would be your partition.

Also, this is a good rule of thumb for using partitions to avoid primary key violations:

Partition by the primary key fields of the target data extension

You must log in to answer this question.

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