1

I have a query that returns records from synchronized Data Extensions.

SELECT c.ID, 
c.AccountID, 
c.Email, 
c.FirstName, 
a.ID AS AccID, 
a.Name AS AccountName,  
p.Related_Agency__c, 
e.Tier__c, 
t.Name AS TierName, 
t.Status__c AS TierStatus,
e.Contacts_at_Location__c,
e.Reward_Amount__c,
FROM [Contact_Salesforce] c
    LEFT JOIN [Engagement__c_Salesforce] e ON c.ID = e.Contact__c
    LEFT JOIN [Account_Salesforce] a ON c.AccountID = a.ID
    LEFT JOIN [Tier__c_Salesforce] t ON e.Tier__c = t.ID
    LEFT JOIN [Program__c_Salesforce] p ON a.ID = p.Related_Agency__c
WHERE c.Focus__c LIKE '%PL%' AND c.Status__c = 'Active'

The query returns Contacts that may have different Contact IDs but the same Contact Email. What I am trying to do is dedupe the records by email address based on the highest Reward_Amount__c.

So if there are records like:

ID, ..., Email, ..., Reward_Amount__c
123, ..., [email protected], ..., 123
232, ..., [email protected], ..., 343
454, ..., [email protected], ..., 234

I would like the result set to be:

232, ..., [email protected], ..., 343
454, ..., [email protected], ..., 234

Basically only adding one record of the duplicate email addresses with the highest Reward_Amount__c

I'm pretty sure I need to use the Rank() or Row_Number() function, but I'm not sure how to use it with all the other fields as well.

Improve this question
3
  • Why do you have two AccountID columns?
    – 0xsegfault
    Oct 13, 2017 at 23:51
  • One is from Contact the other is from Account. I changed the fields a little for simplification. Does that matter? I can remove one of them.
    – victorcorey
    Oct 13, 2017 at 23:56
  • It will throw an error if you try to return two columns with the same name
    – 0xsegfault
    Oct 14, 2017 at 0:12

1 Answer 1

Reset to default
2

You need to add the ROW_NUMBER function in a sub query select from it .

Try this:

SELECT 

c.ID, 
c.AccountID, 
c.Email, 
c.FirstName, 
a.ID AS AccID, 
a.Name AS AccountName,  
p.Related_Agency__c, 
e.Tier__c, 
t.Name AS TierName, 
t.Status__c AS TierStatus,
e.Contacts_at_Location__c,
e.Reward_Amount__c

FROM 

(SELECT c.ID, 
c.AccountID, 
c.Email, 
c.FirstName, 
a.ID AS AccID, 
a.Name AS AccountName,  
p.Related_Agency__c, 
e.Tier__c, 
t.Name AS TierName, 
t.Status__c AS TierStatus,
e.Contacts_at_Location__c,
e.Reward_Amount__c,
ROW_NUMBER()OVER(PARTITION BY c.Email ORDER BY Reward_Amount__c DESC) AS rank
FROM [Contact_Salesforce] c
    LEFT JOIN [Engagement__c_Salesforce] e ON c.ID = e.Contact__c
    LEFT JOIN [Account_Salesforce] a ON c.AccountID = a.ID
    LEFT JOIN [Tier__c_Salesforce] t ON e.Tier__c = t.ID
    LEFT JOIN [Program__c_Salesforce] p ON a.ID = p.Related_Agency__c
WHERE c.Focus__c LIKE '%PL%' AND c.Status__c = 'Active') x
WHERE x.rank = 1
Improve this answer
4
  • I see the partition is c.ID...shouldn't it be Email? There can be different c.ID with the same c.Email. We only want the c.Email with the highest e.Reward_Amount__c. Thanks for your help.
    – victorcorey
    Oct 18, 2017 at 20:39
  • 1
    In that case you can swap it will update
    – 0xsegfault
    Oct 18, 2017 at 20:58
  • can you really use partition by clause in the Sql syntax supported by SFMC Query activities?
    – Eashwer B. Iyer
    Nov 3 at 20:15
  • I havent touched marketing cloud in 5 years , but i dont see why they would take it out
    – 0xsegfault
    Nov 9 at 7:28

You must log in to answer this question.

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