CODEWITHSUNDEEP
mysqlsqlsearchduplicates
cfrederich
cfrederich

Reputation: 1790

How to check for duplicates in mysql table over multiple columns

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?

Upvotes: 26

Views: 28131

Answers (6)

Naresh Chennuri
Naresh Chennuri

Reputation: 1153

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;

Upvotes: 1

RC.
RC.

Reputation: 28197

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;

Upvotes: 63

PRacicot
PRacicot

Reputation: 106

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.

Upvotes: 1

Johan
Johan

Reputation: 76537

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);

Upvotes: 1

Alnitak
Alnitak

Reputation: 339786

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.

Upvotes: 2

manji
manji

Reputation: 47978

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

Upvotes: 4

Related Questions