Is there a way to equate null values to non null values in a partition in SQL?

user_11799469 0
Viewed 37

I have a database table of people records with columns for UserID, FirstName, LastName, DOB, and Email address. FirstName, LastName, and Email are required values, but DOB can be null if the person didn't give that information, so a few rows could look like this:

FirstName          LastName           DOB               Email                      UserID
John               Doe                1990-01-01        johndoe@gmail.com          1
Jane               Doe                1990-02-01        janedoe@gmail.com          2
John               Doe                NULL              johndoe@gmail.com          3
Paul               Blart              1985-01-01        mallcop@gmail.com          4
Clark              Kent               NULL              ImNotSuperman@gmail.com    5
Paul               Blart              1985-01-01        mallcop@gmail.com          6

And I am trying to write a query (that is part of a bigger program) to identify duplicate people records in the database. The requirements are that FirstName, LastName, and Email must be identical, and if there is a value for DOB then it must be identical, but if it is null it can still be labeled as a duplicate. So in the above table, the two John Doe's and the two Paul Blart's would be selected. I want to do this in a partition statement. So my initial attempt is:

SELECT  COUNT(UserID) OVER (Partition BY FirstName, LastName, DOB, Email) AS Count,
DENSE_RANK() OVER (ORDER BY FirstName, LastName, DOB, Email) AS RANK,
UserID, FirstName, LastName, DOB, Email
FROM People
where COUNT(UserID) OVER (Partition BY FirstName, LastName, DOB, Email) > 1

Which correctly selects the Paul Blart's as duplicates but not the John Doe's because one has a null value for DOB. Is there any way to make it so those records are properly selected?

subquery window-functions sql-server sql
2 Answers

This might be simpler expressed with exists:

select t.*
from mytable t
where exists (
    select 1
    from mytable t1
    where 
        t1.id <> t.id
        and t1.firstname = t.firstname
        and t1.lastname = t.lastname
        and t1.email = t.email
        and (t1.dob = t.dob or t1.dob is null or t.dob is null)
)
user_106767160

You can do this using window functions:

select t.*
from (select t.*,
             count(*) over (partition by firstname, lastname, email, dob) as cnt,
             sum(case when dob is null then 1 else 0 end) over (partition by firstname, lastname, email) as cnt_null
      from t
     ) t
where cnt > 1 or 
      (dob is not null and cnt_null > 0);
user_11440350
Related