Reputation: 15121

Find duplicate records in MySQL

I want to pull out duplicate records in a MySQL Database. This can be done with:

SELECT address, count(id) as cnt FROM list
GROUP BY address HAVING cnt > 1

Which results in:

100 MAIN ST    2

I would like to pull it so that it shows each row that is a duplicate. Something like:

JIM    JONES    100 MAIN ST
JOHN   SMITH    100 MAIN ST

Any thoughts on how this can be done? I'm trying to avoid doing the first one then looking up the duplicates with a second query in the code.

Upvotes: 749

Answers (29)

Nowdeen

Reputation: 1450

I saw here several answers that shows the number of duplicates. My answer below show the rows in wich duplication occurs.

SELECT name, sname, address FROM list WHERE address IN ( SELECT address FROM list GROUP BY address HAVING COUNT(address) > 1 );

I have not tested for efficiency, but I just used this query in a 200k rows table with no problems.

Upvotes: 0

aLamp

Reputation: 77

Would go with something like this:

SELECT  t1.firstname t1.lastname t1.address FROM list  t1
    INNER JOIN  list t2 
    WHERE 
        t1.id < t2.id AND 
        t1.address = t2.address;

Upvotes: 0

David

Reputation: 352

SELECT id, count(*) as c  
 FROM 'list'
GROUP BY id HAVING c > 1

This will return you the id with the number of times that id is repeated, or nothing in which case you will not have repeated id.

Change the id in the group by (ex: address) and it will return the number of times an address is repeated identified by the first found id with that address.

SELECT id, count(*) as c  
 FROM 'list'
GROUP BY address HAVING c > 1

I hope it helps. Enjoy ;)

Upvotes: 5

Kar.ma

Reputation: 857

Most of the answers here don't cope with the case when you have MORE THAN ONE duplicate result and/or when you have MORE THAN ONE column to check for duplications. When you are in such case, you can use this query to get all duplicate ids:

SELECT address, email, COUNT(*) AS QUANTITY_DUPLICATES, GROUP_CONCAT(id) AS ID_DUPLICATES
    FROM list
    GROUP BY address, email
    HAVING COUNT(*)>1;

If you want to list every result as a single line, you need a more complex query. This is the one I found working:

CREATE TEMPORARY TABLE IF NOT EXISTS temptable AS (    
    SELECT GROUP_CONCAT(id) AS ID_DUPLICATES
    FROM list
    GROUP BY address, email
    HAVING COUNT(*)>1
); 
SELECT d.* 
    FROM list AS d, temptable AS t 
    WHERE FIND_IN_SET(d.id, t.ID_DUPLICATES) 
    ORDER BY d.id;

Upvotes: 3

Chandan Mistry

Reputation: 371

SELECT * FROM bookings WHERE DATE(created_at) = '2022-01-11' AND code IN ( SELECT code FROM bookings GROUP BY code HAVING COUNT(code) > 1 ) ORDER BY id DESC

Upvotes: 0

rudolfson

Reputation: 4146

Why not just INNER JOIN the table with itself?

SELECT a.firstname, a.lastname, a.address
FROM list a
INNER JOIN list b ON a.address = b.address
WHERE a.id <> b.id

A DISTINCT is needed if the address could exist more than two times.

Upvotes: 217

tim

Reputation: 2732

I use the following:

SELECT * FROM mytable
WHERE id IN (
  SELECT id FROM mytable
  GROUP BY column1, column2, column3
  HAVING count(*) > 1
)

Upvotes: 2

Matt

Reputation: 23789

Finding duplicate addresses is much more complex than it seems, especially if you require accuracy. A MySQL query is not enough in this case...

I work at SmartyStreets, where we do address validation and de-duplication and other stuff, and I've seen a lot of diverse challenges with similar problems.

There are several third-party services which will flag duplicates in a list for you. Doing this solely with a MySQL subquery will not account for differences in address formats and standards. The USPS (for US address) has certain guidelines to make these standard, but only a handful of vendors are certified to perform such operations.

So, I would recommend the best answer for you is to export the table into a CSV file, for instance, and submit it to a capable list processor. One such is LiveAddress which will have it done for you in a few seconds to a few minutes automatically. It will flag duplicate rows with a new field called "Duplicate" and a value of Y in it.

Upvotes: 15

Ganesh Krishnan

Reputation: 7395

To quickly see the duplicate rows you can run a single simple query

Here I am querying the table and listing all duplicate rows with same user_id, market_place and sku:

select user_id, market_place,sku, count(id)as totals from sku_analytics group by user_id, market_place,sku having count(id)>1;

To delete the duplicate row you have to decide which row you want to delete. Eg the one with lower id (usually older) or maybe some other date information. In my case I just want to delete the lower id since the newer id is latest information.

First double check if the right records will be deleted. Here I am selecting the record among duplicates which will be deleted (by unique id).

select a.user_id, a.market_place,a.sku from sku_analytics a inner join sku_analytics b where a.id< b.id and a.user_id= b.user_id and a.market_place= b.market_place and a.sku = b.sku;

Then I run the delete query to delete the dupes:

delete a from sku_analytics a inner join sku_analytics b where a.id< b.id and a.user_id= b.user_id and a.market_place= b.market_place and a.sku = b.sku;

Backup, Double check, verify, verify backup then execute.

Upvotes: 0

Tudor

Reputation: 1020

Isn't this easier :

SELECT *
FROM tc_tariff_groups
GROUP BY group_id
HAVING COUNT(group_id) >1

Upvotes: 53

Usman

Reputation: 575

    Find duplicate Records:

    Suppose we have table : Student 
    student_id int
    student_name varchar
    Records:
    +------------+---------------------+
    | student_id | student_name        |
    +------------+---------------------+
    |        101 | usman               |
    |        101 | usman               |
    |        101 | usman               |
    |        102 | usmanyaqoob         |
    |        103 | muhammadusmanyaqoob |
    |        103 | muhammadusmanyaqoob |
    +------------+---------------------+

    Now we want to see duplicate records
    Use this query:


   select student_name,student_id ,count(*) c from student group by student_id,student_name having c>1;

+--------------------+------------+---+
| student_name        | student_id | c |
+---------------------+------------+---+
| usman               |        101 | 3 |
| muhammadusmanyaqoob |        103 | 2 |
+---------------------+------------+---+

Upvotes: 1

Powerlord

Reputation: 88826

The key is to rewrite this query so that it can be used as a subquery.

SELECT firstname, 
   lastname, 
   list.address 
FROM list
   INNER JOIN (SELECT address
               FROM   list
               GROUP  BY address
               HAVING COUNT(id) > 1) dup
           ON list.address = dup.address;

Upvotes: 768

Arman Malik

Reputation: 851

I tried the best answer chosen for this question, but it confused me somewhat. I actually needed that just on a single field from my table. The following example from this link worked out very well for me:

SELECT COUNT(*) c,title FROM `data` GROUP BY title HAVING c > 1;

Upvotes: 66

Martin Tonev

Reputation: 775

This also will show you how many duplicates have and will order the results without joins

SELECT  `Language` , id, COUNT( id ) AS how_many
FROM  `languages` 
GROUP BY  `Language` 
HAVING how_many >=2
ORDER BY how_many DESC

Upvotes: 8

doublejosh

Reputation: 5774

Find duplicate users by email address with this query...

SELECT users.name, users.uid, users.mail, from_unixtime(created)
FROM users
INNER JOIN (
  SELECT mail
  FROM users
  GROUP BY mail
  HAVING count(mail) > 1
) dupes ON users.mail = dupes.mail
ORDER BY users.mail;

Upvotes: 38

Michał Maluga

Reputation: 473

Powerlord answer is indeed the best and I would recommend one more change: use LIMIT to make sure db would not get overloaded:

SELECT firstname, lastname, list.address FROM list
INNER JOIN (SELECT address FROM list
GROUP BY address HAVING count(id) > 1) dup ON list.address = dup.address
LIMIT 10

It is a good habit to use LIMIT if there is no WHERE and when making joins. Start with small value, check how heavy the query is and then increase the limit.

Upvotes: -1

aad

Reputation: 1

select address from list where address = any (select address from (select address, count(id) cnt from list group by address having cnt > 1 ) as t1) order by address

the inner sub-query returns rows with duplicate address then the outer sub-query returns the address column for address with duplicates. the outer sub-query must return only one column because it used as operand for the operator '= any'

Upvotes: -1

KESAVAN PURUSOTHAMAN

Reputation: 453

we can found the duplicates depends on more then one fields also.For those cases you can use below format.

SELECT COUNT(*), column1, column2 
FROM tablename
GROUP BY column1, column2
HAVING COUNT(*)>1;

Upvotes: 24

pratswinz

Reputation: 515

select `cityname` from `codcities` group by `cityname` having count(*)>=2

This is the similar query you have asked for and its 200% working and easy too. Enjoy!!!

Upvotes: 49

Ionut Petre

Reputation: 41

Personally this query has solved my problem:

SELECT `SUB_ID`, COUNT(SRV_KW_ID) as subscriptions FROM `SUB_SUBSCR` group by SUB_ID, SRV_KW_ID HAVING subscriptions > 1;

What this script does is showing all the subscriber ID's that exists more than once into the table and the number of duplicates found.

This are the table columns:

| SUB_SUBSCR_ID | int(11)     | NO   | PRI | NULL    | auto_increment |
| MSI_ALIAS     | varchar(64) | YES  | UNI | NULL    |                |
| SUB_ID        | int(11)     | NO   | MUL | NULL    |                |    
| SRV_KW_ID     | int(11)     | NO   | MUL | NULL    |                |

Hope it will be helpful for you either!

Upvotes: 4

Lalit Patel

Reputation: 109

SELECT t.*,(select count(*) from city as tt where tt.name=t.name) as count FROM `city` as t where (select count(*) from city as tt where tt.name=t.name) > 1 order by count desc

Replace city with your Table. Replace name with your field name

Upvotes: 3

venkatesh

Reputation: 49

Fastest duplicates removal queries procedure:

/* create temp table with one primary column id */
INSERT INTO temp(id) SELECT MIN(id) FROM list GROUP BY (isbn) HAVING COUNT(*)>1;
DELETE FROM list WHERE id IN (SELECT id FROM temp);
DELETE FROM temp;

Upvotes: 4

Neha Patil

Reputation: 41

select * from table_name t1 inner join (select distinct <attribute list> from table_name as temp)t2 where t1.attribute_name = t2.attribute_name

For your table it would be something like

select * from list l1 inner join (select distinct address from list as list2)l2 where l1.address=l2.address

This query will give you all the distinct address entries in your list table... I am not sure how this will work if you have any primary key values for name, etc..

Upvotes: 4

jerdiggity

Reputation: 3665

Another solution would be to use table aliases, like so:

SELECT p1.id, p2.id, p1.address
FROM list AS p1, list AS p2
WHERE p1.address = p2.address
AND p1.id != p2.id

All you're really doing in this case is taking the original list table, creating two pretend tables -- p1 and p2 -- out of that, and then performing a join on the address column (line 3). The 4th line makes sure that the same record doesn't show up multiple times in your set of results ("duplicate duplicates").

Upvotes: 13

trt

Reputation: 4367

SELECT date FROM logs group by date having count(*) >= 2

Upvotes: 427

DJ.

Reputation: 1035

    SELECT *
    FROM (SELECT  address, COUNT(id) AS cnt
    FROM list
    GROUP BY address
    HAVING ( COUNT(id) > 1 ))

Upvotes: 2

Ryan Roper

Reputation: 333

 SELECT firstname, lastname, address FROM list
 WHERE 
 Address in 
 (SELECT address FROM list
 GROUP BY address
 HAVING count(*) > 1)

Upvotes: 4

Quassnoi

Reputation: 425753

This will select duplicates in one table pass, no subqueries.

SELECT  *
FROM    (
        SELECT  ao.*, (@r := @r + 1) AS rn
        FROM    (
                SELECT  @_address := 'N'
                ) vars,
                (
                SELECT  *
                FROM
                        list a
                ORDER BY
                        address, id
                ) ao
        WHERE   CASE WHEN @_address <> address THEN @r := 0 ELSE 0 END IS NOT NULL
                AND (@_address := address ) IS NOT NULL
        ) aoo
WHERE   rn > 1

This query actially emulates ROW_NUMBER() present in Oracle and SQL Server

See the article in my blog for details:

Analytic functions: SUM, AVG, ROW_NUMBER - emulating in MySQL.

Upvotes: 11

Chad Birch

Reputation: 74638

Not going to be very efficient, but it should work:

SELECT *
FROM list AS outer
WHERE (SELECT COUNT(*)
        FROM list AS inner
        WHERE inner.address = outer.address) > 1;

Upvotes: 12

Find duplicate records in MySQL

Answers (29)

Related Questions