Skip to main content
R
RichardODreamweaver
Inspiring
March 13, 2007
Answered

Find Duplicate Query MySQL Syntax

  • March 13, 2007
  • 2 replies
  • 406 views
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?
This topic has been closed for replies.
Correct answer Newsgroup_User
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/

2 replies

R
RichardODreamweaverAuthor
Inspiring
March 14, 2007
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.
N
Newsgroup_UserCorrect answer
Inspiring
March 13, 2007
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/
Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices