SQL query to count duplicates [duplicate]

I agree with all the comments so please see How To Ask

In the off chance this works for you, and in the interests of it being a good excercise for myself and it helping anyone else to stumbles upon this, here is some GENERIC code that will do it:

 WITH CTE AS ( SELECT COALESCE(Col1,'') AS Col1, COALESCE(Col2,'') AS Col2, ROW_NUMBER() OVER(PARTITION BY col1,col2 ORDER BY col1,col2) AS row_id FROM MyTable ) DELETE CTE WHERE row_id >1; 

Example:

Create table #things (FirstName varchar(10), LastName varchar(10)) insert into #things (FirstName, LastName) values('thing','lastthing'),('thing','lastthing'),('otherthing', 'something') select * from #things ;WITH CTE AS ( SELECT COALESCE(firstname,'') AS Col1, COALESCE(lastname,'') AS Col2, ROW_NUMBER() OVER(PARTITION BY firstname,lastname ORDER BY firstname,lastname) AS row_id FROM #things ) DELETE CTE WHERE row_id >1; select * from #things drop table #things