Finding duplicate values in MySQL

Question

I have a table with a varchar column  and I would like to find all the records that have duplicate values in this column  What is the best query I can use to find the duplicates

User · Answer

Try using this query   SELECT name  COUNT    value count FROM company master GROUP BY name HAVING value count  gt  1

User · Answer

SELECT    FROM  dps   WHERE pid IN  SELECT pid FROM  dps  GROUP BY pid HAVING COUNT pid  gt 1

User · Answer

One very late contribution    in case it helps anyone waaaaaay down the line    I had a task to find matching pairs of transactions  actually both sides of account-to-account transfers  in a banking app  to identify which ones were the  from  and  to  for each inter-account-transfer transaction  so we ended up with this   SELECT      LEAST primaryid  secondaryid  AS transactionid1      GREATEST primaryid  secondaryid  AS transactionid2 FROM       SELECT table1 transactionid AS primaryid           table2 transactionid AS secondaryid     FROM financial transactions table1     INNER JOIN financial transactions table2      ON table1 accountid   table2 accountid     AND table1 transactionid  lt  gt  table2 transactionid      AND table1 transactiondate   table2 transactiondate     AND table1 sourceref   table2 destinationref     AND table1 amount    0 - table2 amount    AS DuplicateResultsTable GROUP BY transactionid1 ORDER BY transactionid1    The result is that the DuplicateResultsTable provides rows containing matching  i e  duplicate  transactions  but it also provides the same transaction id s in reverse the second time it matches the same pair  so the outer SELECT is there to group by the first transaction ID  which is done by using LEAST and GREATEST to make sure the two transactionid s are always in the same order in the results  which makes it safe to GROUP by the first one  thus eliminating all the duplicate matches  Ran through nearly a million records and identified 12 000  matches in just under 2 seconds  Of course the transactionid is the primary index  which really helped

User · Answer

If you want to remove duplicate use DISTINCT   Otherwise use this query   SELECT users   COUNT user ID  as user FROM users GROUP BY user name HAVING user  gt  1

User · Answer

My final query incorporated a few of the answers here that helped - combining group by  count  amp  GROUP CONCAT   SELECT GROUP CONCAT id    magento simple   COUNT    c  FROM product variant  GROUP BY  magento simple  HAVING c  gt  1    This provides the id of both examples  comma separated   the barcode I needed  and how many duplicates   Change table and columns accordingly

User · Answer

For removing duplicate rows with multiple fields   first cancate them to the new unique key which is specified for the only distinct rows  then use  group by  command to removing duplicate rows with the same new unique key   Create TEMPORARY table tmp select concat f1 f2  as cfs t1   from mytable as t1  Create index x tmp cfs on tmp cfs   Create table unduptable select f1 f2     from tmp group by cfs

User · Answer

to get all the data that contains duplication i used this   SELECT   FROM TableName INNER JOIN    SELECT DupliactedData FROM TableName GROUP BY DupliactedData HAVING COUNT DupliactedData   gt  1 order by DupliactedData    temp ON TableName DupliactedData   temp DupliactedData    TableName   the table you are working with   DupliactedData   the duplicated data you are looking for

User · Answer

CREATE TABLE tbl master       id  int   email  varchar 15     INSERT INTO tbl master       id    email   VALUES      1   test1 gmail com         2   test2 gmail com         3   test1 gmail com         4   test2 gmail com         5   test5 gmail com     QUERY   SELECT id  email FROM tbl master WHERE email IN  SELECT email FROM tbl master GROUP BY email HAVING COUNT id   gt  1

User · Answer

To find how many records are duplicates in name column in Employee  the query below is helpful   Select name from employee group by name having count    gt 1

User · Answer

I saw the above result and query will work fine if you need to check single column value which are duplicate  For example email   But if you need to check with more columns and would like to check the combination of the result so this query will work fine   SELECT COUNT CONCAT name email   AS tot         name         email FROM users GROUP BY CONCAT name email  HAVING tot gt 1  This query will SHOW the USER list which ARE greater THAN 1               AND also COUNT

User · Answer

The following will find all product id that are used more than once  You only get a single record for each product id   SELECT product id FROM oc product reward GROUP BY product id HAVING count  product id    gt 1   Code taken from   http   chandreshrana blogspot in 2014 12 find-duplicate-records-based-on-any html

User · Answer

I improved from this  SELECT      col       COUNT col  FROM     table name GROUP BY col HAVING COUNT col   gt  1

User · Answer

SELECT DISTINCT a email FROM  users  a LEFT JOIN  users  b ON a email   b email WHERE a id    b id

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

User · Answer

SELECT    FROM    mytable mto WHERE   EXISTS                   SELECT  1         FROM    mytable mti         WHERE   mti varchar column   mto varchar column         LIMIT 1  1            This query returns complete records  not just distinct varchar column s  This query doesn t use COUNT     If there are lots of duplicates  COUNT    is expensive  and you don t need the whole COUNT     you just need to know if there are two rows with same value  This is achieved by the LIMIT 1  1 at the bottom of the correlated query  essentially meaning  quot return the second row quot    EXISTS would only return true if the aforementioned second row exists  i  e  there are at least two rows with the same value of varchar column    Having an index on varchar column will  of course  speed up this query greatly

User · Answer

As a variation on Levik s answer that allows you to find also the ids of the duplicate results  I used the following  SELECT   FROM table1 WHERE column1 IN  SELECT column1 AS duplicate value FROM table1 GROUP BY column1 HAVING COUNT     gt  1

User · Answer

Taking  maxyfc s answer further  I needed to find all of the rows that were returned with the duplicate values  so I could edit them in MySQL Workbench   SELECT   FROM table    WHERE field IN        SELECT field FROM table GROUP BY field HAVING count     gt  1      ORDER BY field

User · Answer

SELECT varchar col FROM table GROUP BY varchar col HAVING COUNT     gt  1

User · Answer

I prefer to use windowed functions MySQL 8 0   to find duplicates because I could see entire row   WITH cte AS     SELECT        COUNT    OVER PARTITION BY col name  AS num of duplicates group      ROW NUMBER   OVER PARTITION BY col name ORDER BY col name2  AS pos in group   FROM table   SELECT   FROM cte WHERE num of duplicates group  gt  1    DB Fiddle Demo

User · Answer

Building off of levik s answer to get the IDs of the duplicate rows you can do a GROUP CONCAT if your server supports it  this will return a comma separated list of ids    SELECT GROUP CONCAT id   name  COUNT    c FROM documents GROUP BY name HAVING c  gt  1

User · Answer

Select column name  column name1 column name2  count 1  as temp from table name group by column name having temp  gt  1

User · Answer

I am not seeing any JOIN approaches  which have many uses in terms of duplicates    This approach gives you actual doubled results   SELECT t1   FROM my table as t1  LEFT JOIN my table as t2  ON t1 name t2 name and t1 id  t2 id  WHERE t2 id IS NOT NULL  ORDER BY t1 name

User · Answer

Do a SELECT with a GROUP BY clause  Let s say name is the column you want to find duplicates in   SELECT name  COUNT    c FROM table GROUP BY name HAVING c  gt  1    This will return a result with the name value in the first column  and a count of how many times that value appears in the second

User · Answer

SELECT ColumnA  COUNT      FROM Table GROUP BY ColumnA HAVING COUNT       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

Assuming your table is named TableABC and the column which you want is Col and the primary key to T1 is Key   SELECT a Key  b Key  a Col  FROM TableABC a  TableABC b WHERE a Col   b Col  AND a Key  lt  gt  b Key   The advantage of this approach over the above answer is it gives the Key

[mysql] Finding duplicate values in MySQL

Examples related to mysql