Find duplicate records in MySQL

Question

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  gt  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

User · Answer

select   from table name t1 inner join  select distinct  lt attribute list gt  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

User · Answer

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  gt  1

User · Answer

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  gt  2 ORDER BY how many DESC

User · Answer

select  cityname  from  codcities  group by  cityname  having count    gt  2   This is the similar query you have asked for and its 200  working and easy too  Enjoy

User · Answer

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    gt 1

User · Answer

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

User · Answer

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   gt  1   dupes ON users mail   dupes mail ORDER BY users mail

User · Answer

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  gt  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

User · Answer

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  lt  gt  address THEN  r    0 ELSE 0 END IS NOT NULL                 AND    address    address   IS NOT NULL           aoo WHERE   rn  gt  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

User · Answer

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   gt  1  dup            ON list address   dup address

User · Answer

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  gt 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 lt  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 lt  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

User · Answer

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   gt  1

User · Answer

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   gt  1 order by count desc   Replace city with your Table  Replace name with your field name

User · Answer

SELECT date FROM logs group by date having count     gt   2

User · Answer

Isn t this easier    SELECT   FROM tc tariff groups GROUP BY group id HAVING COUNT group id   gt 1

User · Answer

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  lt  gt  b id   A DISTINCT is needed if the address could exist more than two times

User · Answer

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   gt  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

User · Answer

select address from list where address   any  select address from  select address  count id  cnt from list group by address having cnt  gt  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

User · Answer

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    gt 1  DELETE FROM list WHERE id IN  SELECT id FROM temp   DELETE FROM temp

User · Answer

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 gt 1    -------------------- ------------ ---    student name          student id   c    --------------------- ------------ ---    usman                        101   3     muhammadusmanyaqoob          103   2    --------------------- ------------ ---

User · Answer

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  quot Duplicate quot  and a value of Y in it

User · Answer

SELECT firstname  lastname  address FROM list  WHERE   Address in    SELECT address FROM list  GROUP BY address  HAVING count     gt  1

User · Answer

SELECT       FROM  SELECT  address  COUNT id  AS cnt     FROM list     GROUP BY address     HAVING   COUNT id   gt  1

[mysql] Find duplicate records in MySQL

Examples related to mysql

Examples related to duplicates