Partition by using multiple case statements

Question

I am attempting to dedupe records in a database using partition by clause This is the query i run in order to dedupe. It ranks the records that are the most populated and keeps the highest ranked record.

   WITH cteDupes AS 
(
-- 
-- Partition based on contact.owner and email
    SELECT  ROW_NUMBER() OVER(PARTITION BY contactowner, email
 ORDER BY 
 -- ranking by populated field
    case when otherstreet is not null then 1 else 0 end +
    case when othercity is not null then 1 else 0 end 
) AS RND, *
    FROM  scontact
    where (contact_owner_name__c is not null and contact_owner_name__c<>'') and (email is not null and email<>'')
)
--Rank data and place it into a new table created
select * into contact_case1
from cteDupes
WHERE RND=1;

I wanted to know if its possible to partition by using case. For example currently i am partitioning by contactowner and email. When contactowner is null i want to partition by contactofficer instead. Can I create case statements like this or is this not possible since the ranking will be altered in someway.

Answer 1

You can use case, but I think coalesce() is simpler in this case:

SELECT ROW_NUMBER() OVER (PARTITION BY COALESCE(contactowner, contactofficer), email
                          . . .

If you want contacts and officers with the same name to be counted separately, then you would do:

SELECT ROW_NUMBER() OVER (PARTITION BY (CASE WHEN contactowner is NULL then 1 else 2 end),
                                       contactowner,
                                       (CASE WHEN contactowner is null THEN contactofficer END),
                                       email
                           . . .

Answer 2

So ContactOfficer should only come into play for partitioning purposes if contactOwner is null?

In that case you can use.

PARTITION BY contactowner, 
             CASE WHEN contactOwner IS NULL THEN contactofficer END,
             email  . . .