How to check for duplicates in mysql table over multiple columns

Question

I have a table of baseball players(all 1000 or so), with fields:

mysql> describe person;
+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| id        | int(11)     | NO   | PRI | NULL    | auto_increment |
| firstname | varchar(30) | NO   |     | NULL    |                |
| lastname  | varchar(30) | NO   |     | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+

But I think there are some players that have gotten added in twice. How can I go through and check for how many occurrences of a particular firstname, lastname combo?

Do what @RC said, then add a `(firstname,lastname)` unique key — Mikhail, Jun 23 '11 at 13:31

score 63 · Accepted Answer · edited Jun 23 '11 at 14:17

63

This provides the list of duplicates:

SELECT firstname, lastname, COUNT(*) 
FROM person 
GROUP BY firstname, lastname 
HAVING COUNT(*) > 1;

If you want to see the counts for every row remove the having clause:

SELECT firstname, lastname, COUNT(*) 
FROM person 
GROUP BY firstname, lastname;

edited Jun 23 '11 at 14:17

cEz

4,932
1
25
38

answered Jun 23 '11 at 13:27

RC.

27,409
9
73
93

3

Clearly the best answer possible. – Jesús Flores Oct 19 '12 at 14:20

score 4 · Answer 2 · answered Jun 23 '11 at 13:27

4

SELECT firstname, lastname, count(id) count
  FROM person
 WHERE firstname = ?
   AND lasttname = ?
 GROUP BY firstname, lastname

answered Jun 23 '11 at 13:27

manji

47,442
5
96
103

that only tells you _if_ a particular person is a duplicate, not _which_ persons are duplicated. – Alnitak Jun 23 '11 at 13:44
ah, yes, I see what you mean. I believe he meant _particular_ as in _"distinct"_, rather than _"specific"_. I was similarly vague in my comment, where I did indeed mean _"specific"_ ! :) – Alnitak Jun 23 '11 at 13:53

score 2 · Answer 3 · answered Jun 23 '11 at 13:29

For a list sorted by decreasing value of the number of copies:

SELECT firstname, lastname, COUNT(*) AS n
  FROM person
 GROUP BY firstname, lastname
 ORDER BY n DESC
 HAVING n > 1

The HAVING clause is the key part - it's necessary to filter the results after the GROUP BY clause, since a WHERE clause filters out rows before they're grouped.

score 1 · Answer 4 · edited Oct 06 '15 at 09:47

To find duplicate records (ex: to find login name and password combination of duplicate records) in a table use the below query;

SELECT em.* FROM employee_master AS em JOIN 
 (SELECT emp.login, emp.password, COUNT(*) 
  FROM employee_master emp 
  WHERE emp.login != '' AND emp.password != '' 
  GROUP BY emp.login, emp.PASSWORD
  HAVING COUNT(*) > 1
 ) AS dl 
WHERE em.login =  dl.login AND em.password = dl.password;

score 1 · Answer 5 · answered Jun 23 '11 at 13:30

1

To get id's of duplicate names as well as names do:

SELECT p1.id, p1.firstname, p1,lastname FROM person p1
INNER JOIN person p2 ON (p1.firstname = p2.firstname 
                         AND p1.lastname = p1.lastname 
                         AND p1.id <> p2.id);

answered Jun 23 '11 at 13:30

Johan

74,508
24
191
319

@Alnitak, don't listen to what I say, listen to what I mean :-). – Johan Jun 23 '11 at 13:32

score 1 · Answer 6 · answered Jun 23 '11 at 13:37

If you simply want to erase all the duplicate, you could do a temporary table and fill it up with all youre data except the duplicate and them re-update youre primary table.

The query to select the data with duplicate would be this

 SELECT DISTINCT firstname, lastname FROM table

To get the complete list of data in you're table

SELECT firstname, lastname, COUNT(*) AS n
  FROM person
 GROUP BY firstname, lastname
 ORDER BY lastname DESC
 HAVING n > 1

With this last query you'll get a the list of data sorted by lastname Alphabeticly.

How to check for duplicates in mysql table over multiple columns

6 Answers6

Linked