Find Duplicate Query MySQL Syntax

[RichardODreamweaver]

There is a "find duplicates" query in SQL that is very useful but I'm not sure what the syntax is for MySQL. It probably has something to do with select distinct, group by but I'm not used to to using these.

This is one that finds all duplicates of [FULLNAME]

SELECT CONTACTS.*
WHERE (((CONTACTS.FULLNAME) In (SELECT [FULLNAME] FROM [CONTACTS] As Tmp GROUP BY [FULLNAME]
HAVING Count(*)>1 )))
ORDER BY CONTACTS.FULLNAME, CONTACTS.COMPANY

Does anyone know?

[Newsgroup_User · Accepted solution]

RichardODreamweaver wrote:
> There is a "find duplicates" query in SQL that is very useful but I'm not sure
> what the syntax is for MySQL.

SELECT COUNT(*) AS repetitions, FULLNAME, COMPANY
FROM CONTACTS
GROUP BY FULLNAME, COMPANY
HAVING repetitions > 1

It's not something I have used myself, but I got it from MySQL Cookbook
by Paul DuBois, published by O'Reilly. I have the first edition, which
covers MySQL 4.0. I think the second edition covers MySQL 5.0.

If you're using MySQL on a regular basis, you or your employer should
buy it immediately. It will solve most of your problems and repay its
cost many times over.

--
David Powers, Adobe Community Expert
Author, "Foundation PHP for Dreamweaver 8" (friends of ED)
Author, "PHP Solutions" (friends of ED)
http://foundationphp.com/

[RichardODreamweaver]

Thanks David - sort of does what I wanted but again - the GROUP function condenses the information.

What would have been ideal is to show each duplicate as a separate row such as:

Mr John Smith - John Smith Inc - Last Updated 2005
Mr John Smith - Alan Smith Ltd - Last Updated 2007
Mr James Batton - Microsoft - Last Updated 2003
James Batton - Computer Development Systems - Last Updated 2005

etc.
etc.

This would quickly highlight what the duplicates are but also which duplicate is more likely to be wrong and which can be deleted.

The solution you suggested is fine though - thanks.