SQL Server : Remove Duplicate Records by Great Than ContactID

Have been working with the following query to help me list and find duplicates within my table.

SELECT TOP 100 
    a.ContactID, a.[Company Name], a.PhoneNumber, a.Status, a.State
FROM  
    Contacts a
JOIN 
    (SELECT PhoneNumber, Status, COUNT(PhoneNumber) AS totals
     FROM Contacts
     GROUP BY PhoneNumber, Status
     HAVING COUNT(PhoneNumber) > 1) b ON a.PhoneNumber = b.PhoneNumber
                                      AND a.Status = 'New'
                                      AND a.Status = b.Status
ORDER BY 
    a.PhoneNumber ASC

This returns just what I am searching for in the following:

1365640108  MOBILTIY SHOP   2242057949  New IL
1365647384  MOBILITY SHOP   2242057949  New IL
1365640142  LIFE POINT CHILD  FAMILY    2242085228  New IL
1365647390  PROGAR MICHELLE 2242085228  New IL
1365640150  WILLIAM RYAN HOMES  2242098617  New IL
1365647393  WILLIAM RYAN HOMES  2242098617  New IL
1365640153  TRAVELHERO.COM  2242101344  New IL
1365647398  TRAVELHERO.COM  2242101344  New IL
1365645613  PFIZER INC  2242122000  New IL
1365645614  HOSPIRA WORLDWIDE INC   2242122000  New IL

Now, I want to delete the record that has the GREATER ContactID than the other duplicate record.

That is where I am stuck. I have scoured the search results but have not been able to find anything that fits this puzzle.

Thoughts?

Answer

You could use row_number() and a CTE. I think that this does what you want:

with cte as (
    select row_number() over(partition by PhoneNumber, Status order by ContactID) rn
    from contacts
    where status = 'New'
)
delete from cte where rn > 1

The query deletes records for which another record exists with the same PhoneNumber and Status and a smallest ContactID.

Advertisement

Answer