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.