Search code examples
sqlsql-serversql-delete

Remove duplicates records on database SQL Server

If I have a table leads

id|phone|name
__|_____|_____
1 |123  |Test
2 |123  |Test
3 |111  |Test2
4 |123  |Test

How Do I remove the duplicate phone numbers in there? So if there is 3 records with 123 as phone number, the other 2 duplicates should be delete.

Solution 
  • ;WITH cte AS 
(
    SELECT *, RowNum = ROW_NUMBER() OVER (PARTITION BY phone, name ORDER BY id)
    FROM tbl    
)
DELETE FROM cte
WHERE RowNum > 1