SQL query to count duplicates

Question

Please assist me to get a query to count duplicates and a query to delete the duplicates.

https://www.google.co.uk/search?q=sql+query+count+duplicates — Tom Lord, May 11 '17 at 14:28
Read https://stackoverflow.com/help/how-to-ask. Then add sample table data and the expected result - all as well formatted text. And show us your current query attempt(s). And tag the dbms you're using! — jarlh, May 11 '17 at 14:30
try this DELETE FROM tblUsers WHERE ID IN (SELECT ID FROM tblUsers GROUP BY name, email HAVING COUNT(*) > 1) — Minhaj Patel, May 11 '17 at 14:49

score 1 · Answer 1 · edited May 23 '17 at 10:31

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

SQL query to count duplicates

1 Answers1