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
);

Answer 3

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

http://stackoverflow.com/questions/182544/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).