Find mysql duplicate records

610 0 0 0

Last Updated : 2024-06-25 19:11:16

If you have a mysql table and want to retrieve which are duplicated for one or more rows, here is how to do this : 1) You have to group results by the column(s) itself 2) Select the column(s) and its count as well 3) Use having to only get those that their count is greater than 1

SELECT 
col,
COUNT(col)
FROM
table_name
GROUP BY col
HAVING COUNT(col) > 1;
//-------------------------------------- EX
SELECT
email,
COUNT(email)
FROM
contacts
GROUP BY email
HAVING COUNT(email) > 1;

SELECT 
col1, COUNT(col1),
col2, COUNT(col2),
...

FROM
table_name
GROUP BY
col1,
col2, ...
HAVING
(COUNT(col1) > 1) AND
(COUNT(col2) > 1) AND
...
//-------------------------------------- EX
SELECT
first_name, COUNT(first_name),
last_name, COUNT(last_name),
email, COUNT(email)
FROM
contacts
GROUP BY
first_name ,
last_name ,
email
HAVING COUNT(first_name) > 1
AND COUNT(last_name) > 1
AND COUNT(email) > 1;

//------------------------------- EX2
SELECT
id, parlNumber, SESSION, billNumberPrefix, billNumber, title, originatingChamber, introduced_date,
COUNT(parlNumber) AS parlNumberCount,
COUNT(SESSION) AS sessionCount,
COUNT(billNumberPrefix) AS billNumberPrefixCount,
COUNT(billNumber) AS billNumberCount
FROM
billsfederal
GROUP BY
parlNumber ,
SESSION ,
billNumberPrefix,
billNumber
HAVING COUNT(parlNumber) > 1
AND COUNT(session) > 1
AND COUNT(billNumberPrefix) > 1
AND COUNT(billNumber) > 1
;

//------------------------------------- EX3
SELECT
id, parlNumber, SESSION, billNumberPrefix, billNumber, title, originatingChamber, introduced_date,
COUNT(parlNumber) AS parlNumberCount,
COUNT(SESSION) AS sessionCount,
COUNT(billNumberPrefix) AS billNumberPrefixCount,
COUNT(billNumber) AS billNumberCount,
COUNT(title) AS titleCount
FROM
billsfederal
GROUP BY
parlNumber ,
SESSION ,
billNumberPrefix,
billNumber,
title
HAVING COUNT(parlNumber) > 1
AND COUNT(session) > 1
AND COUNT(billNumberPrefix) > 1
AND COUNT(billNumber) > 1
AND COUNT(title) > 1
;

Mohammed Anwar

Mohammed Anwar

Experienced technical lead PHP, MySQL and Laravel Developer for 15+ years, with proven ability to develop and create high-quality and optimized web applications. Great ability to build and optimize database design, schema and queries. Versed programing trainer and instructor delivering web courses and helping others to get into the field in a timely manner. Fast and eager learner for new technologies .