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.