Fastest "Get Duplicates" SQL script

mm2010_3535708 0
Viewed 38073

What is an example of a fast SQL to get duplicates in datasets with hundreds of thousands of records. I typically use something like:

SELECT afield1, afield2 FROM afile a 
WHERE 1 < (SELECT count(afield1) FROM afile b WHERE a.afield1 = b.afield1);

But this is quite slow.

duplicates scripting sql performance
5 Answers

This is the more direct way:

select afield1,count(afield1) from atable 
group by afield1 having count(afield1) > 1
Vinko_Vrsalovic_51900

You could try:

select afield1, afield2 from afile a
where afield1 in
( select afield1
  from afile
  group by afield1
  having count(*) > 1
);
user_187470

A similar question was asked last week. There are some good answers there.

SQL to find duplicate entries (within a group)

In that question, the OP was interested in all the columns (fields) in the table (file), but rows belonged in the same group if they had the same key value (afield1).

There are three kinds of answers:

subqueries in the where clause, like some of the other answers in here.

an inner join between the table and the groups viewed as a table (my answer)

and analytic queries (something that's new to me).

Walter_Mitty_199370

By the way, if anyone wants to remove the duplicates, I have used this:

delete from MyTable where MyTableID in (
  select max(MyTableID)
  from MyTable
  group by Thing1, Thing2, Thing3
  having count(*) > 1
)
user_114610
Related