Finding duplicates in two fields

[peteraub]

Hi,
i have a table with client details. I would like to find all records where the first name and last name are the same. e.g. if John Smith was in there twice or more.

Can someone help me with a query which would achieve this.
After I have found the duplicate entries I will then do maintence on the table.

Thanks🙂

[crimaniak]

SELECT FirstName,LastName,COUNT(*) as cnt
FROM tablename
GROUP BY FirstName,LastName
HAVING cnt>1
ORDER BY cnt DESC

[mourisj]

The code provided by crimaniak lists the names, firstnames of the persons, but not the different IDs concerned.

Depending on what your database allows you to do (e.g. ORACLE does), you can also get the IDs with a query like this:

select PID, FirsName, Lastname
FROM tablename
where Firstname, Lastname in (
SELECT FirstName,LastName,COUNT(*) as cnt
FROM tablename
GROUP BY FirstName,LastName
HAVING cnt>1 )
order by FirstName, Lastname

Note that you might have more than one "Jon Smith" in your database, being really different people!

Have a nice day,

JJ Mouris