How to find Duplicate Entries in a mySQL Database
Here's a quick easy query that will return all records where a given column's value appears more than once.
The trick is the 'group by' phrase, combined with the selection of the 'count(*)' value, which simply counts the records found for each match of 'Name'.
This query will return any records where the same value appears more than one time in the given column (in this case, the 'Name' column), along with the 'Counter' value showing how many times the duplicate appears.
SELECT Name,count(*) as Counter
FROM Table_Name
GROUP BY Name
HAVING Counter > 1
SELECT *
FROM Table_Name
WHERE Name IN
(
SELECT Name
FROM Table_Name
GROUP BY Name
HAVING count(*) > 1
)
ORDER BY Name
thanks, that will surely come in handy, too!
SELECT
num,
nom,
prenom
FROM t_doublon
UNION
SELECT
num,
nom,
prenom
FROM t_doublon;
http://www.cfforum.eu/viewtopic.php?f=5&t=220