Find and Display duplicates

datona

I have a MySQL table called clients.
This table's columns are:

FirstName | LastName | address | etc......

What I would like to do is dispay all rows in the database which are DUPLICATE having the same FirstName and LastName

I have tried:

SELECT first_name, last_name, COUNT( first_name ) , COUNT( last_name ) 
FROM  `clients` 
GROUP  BY first_name, last_name
HAVING count( first_name )  >1 AND count( last_name )  >1 
ORDER  BY last_name

But what I happening is that it will ONLY list one match per line.
What I need it to do is say if there are two "Betty Bishop" lines in the database, list both of them so I can integrate this into a form with checkboxes to remove one of them.
I want to display all because I would like to compare the two to see which one I would like to remove.

Thanks for your help!

datona

BUMP

xblue

imho aggregate functions simply don't work that way.

you might want to play around with self joins instead, sth like

SELECT  t1.id, t1.firstname, t1.lastname, t1.address 
FROM clients t1, clients t2 
WHERE t1.firstname = t2.firstname
AND t1.lastname = t2.lastname 
AND t1.id <> t2.id

(if there may occur more two recordsets with the same name, you'd get each of these listed more than once. then you'd probably use DISTINCT on the unique column)

datona

Thanks Xblue but I assume that your coding is referencing TWO tables.
I only have one table for clients.
I did attempt to run this (edited ofcouse) but it just maxed out the CPU usage and stalled hung there.

Any ideas?
Thanks

sidney

use xblues query as is

it references one table clients

with its self

FROM clients t1, clients t2

datona

Yes this query works (Thanks Xblue) but I am not sure if there is any way to speed it up somewhat because when I run it, it takes around 405 seconds (6 minutes 45 seconds) to execute.
This is the code I am using:

SELECT t1.cust_id, t1.first_name, t1.last_name
FROM clients t1, clients t2
WHERE t1.first_name = t2.first_name AND t1.last_name = t2.last_name AND t1.cust_id <> t2.cust_id
ORDER BY last_name, first_name

I have 18,000 records in the database (45 columns)
I am attempting to make this a function that the client can run through a php interface but I don't want them to wait 6-7 minutes for it to display the result nor do I want it to use all the system resources for that length of time.

Any suggestions?

xblue

hi again,

I wouldn't know how to optimize the query, but you might consider to change the design:

use the original query which only lists one of each duplicate entries, and make that a link (or form) to another script (or handle it in the same script, doesn't make much difference) which pulls the related recordsets with delete option.

ok, 1 click more for the user, but if that saves 6+ min, it may be well worth it in my opinion.