Fastest "Get Duplicates" SQL script

sql scripting duplicates performance
Johan Bresler picture Johan Bresler · Oct 13, 2008 · Viewed 36.8k times · Source

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

Vinko Vrsalovic picture Vinko Vrsalovic · Oct 13, 2008

This is the more direct way:

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

Related questions

SQL Server - stop or break execution of a SQL script

Is there a way to immediately stop execution of a SQL script in SQL server, like a "break" or "exit" command? I have a script that does some validation and lookups before it starts doing inserts, and I want it …

Read More
How to execute an .SQL script file using c#

I'm sure this question has been answered already, however I was unable to find an answer using the search tool. Using c# I'd like to run a .sql file. The sql file contains multiple sql statements, some of which are …

Read More
Loading .sql files from within PHP

I'm creating an installation script for an application that I'm developing and need to create databases dynamically from within PHP. I've got it to create the database but now I need to load in several .sql files. I had planned …

Read More