I am trying write a query that will result in duplicate SFMC contacts in a SFMC list.
More specifically, I have 3 brands and each has a shared publication list then there is the All subscriber List. Let's say I have a subscriber with the email address firstname.lastname@example.org who is in the lists as follows.(note that each subscriberKey is unique)
EmailAddress ListName SubscriberKey email@example.com BrandA 0031500001dpVxEAAU firstname.lastname@example.org BrandA 0031500001dpVyjAAE email@example.com BrandA 0031500001dpVy7AAE firstname.lastname@example.org BrandB 0031500001dpVxUAAU email@example.com BrandC 0031500001dpVxNAAU firstname.lastname@example.org All Subscribers 0031500001dpVxNAAU
I need a query that can identify that this emailAddress exists on List BrandA 3 times and will give me all but 1 of those subscriber keys.
(The goal is that I will delete the results of the query from the contacts keeping only one contact per brand list or all subscriber list if they are not in any brand list)
This is an example of what does NOT give me what I need:
SELECT EmailAddress , SubscriberKey , ListName FROM _ListSubscribers WHERE EmailAddress IN ( SELECT EmailAddress FROM _ListSubscribers GROUP BY EmailAddress HAVING COUNT(*) > 1 )