Fastest "Get Duplicates" SQL script

Question

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.

Answer 1

This is the more direct way:

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

Answer 2

You could try:

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