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
– Mikhail, Commented Jun 23, 2011 at 13:31

cEz · Accepted Answer · 2011-06-23 14:17:53Z

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, 2011 at 14:17

cEz

5,0621 gold badge28 silver badges38 bronze badges

answered Jun 23, 2011 at 13:27

RC.

28.5k10 gold badges80 silver badges94 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jesús Flores Over a year ago

Clearly the best answer possible.

manji · Accepted Answer · 2011-06-23 13:27:30Z

4

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

answered Jun 23, 2011 at 13:27

manji

48k5 gold badges98 silver badges104 bronze badges

2 Comments

Alnitak Over a year ago

that only tells you if a particular person is a duplicate, not which persons are duplicated.

Alnitak Over a year ago

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 · Accepted Answer · 2011-06-23 13:29:43Z

2

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.

answered Jun 23, 2011 at 13:29

Alnitak

341k72 gold badges420 silver badges503 bronze badges

Comments

Johan · Accepted Answer · 2011-06-23 13:30:48Z

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, 2011 at 13:30

Johan

77.4k28 gold badges204 silver badges346 bronze badges

1 Comment

Johan Over a year ago

@Alnitak, don't listen to what I say, listen to what I mean :-).

PRacicot · Accepted Answer · 2011-06-23 13:37:24Z

1

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.

answered Jun 23, 2011 at 13:37

PRacicot

1061 silver badge9 bronze badges

Comments

nowhere · Accepted Answer · 2015-10-06 09:47:32Z

1

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;

edited Oct 6, 2015 at 9:47

nowhere

1,5481 gold badge12 silver badges31 bronze badges

answered Oct 6, 2015 at 8:51

Naresh Chennuri

1,16312 silver badges10 bronze badges

Collectives™ on Stack Overflow

How to check for duplicates in mysql table over multiple columns

6 Answers 6

1 Comment

2 Comments

Comments

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

1 Comment

2 Comments

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related