MS SQL Tips & Tricks

How to find duplicates in the table

There are several possible ways of finding duplicates in table. The first solution looks for the similar record to each one that has different Identity. That is enough to make sure that this records are duplicates. 

select *
from  Table t
where exists(
      Select t2.Id from Table t2
      where t.Column=t2.Column and
           t.Id<>t2.Id   
   )

Second way does the same thing but uses subquery instead of exist clause. 

select *
from Table t
inner join (
   select count(*) as Items, Column from Table
   group by Column
   having Count(*)>1   
) as pom on (pom.Column=t.Column)

In both queries you can always extend the condition 

select *
from Table t
inner join (
   select count(*) as Items, Column1, COlumn2, Column3 
   from Table
   group by Column1, COlumn2, Column3
   having Count(*)>1   
) as pom on (pom.Column1=t.Column1) and
	(pom.Column2=t.Column2)
	(pom.Column3=t.Column3)

See also


Learn more tricks

Add Comment

Comments