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?

Advantage

Since you mentioned find all records, I am assuming you need to know the KEYS as well as the duplicated VALUES in that varchar column. – Christy

I can find the keys easy enough after I get the values, I really just want a list of all the duplicate values. – Advantage

phoenixnap.com/kb/mysql-find-duplicates – Batista

Answer 1

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

Dialect

But how is this useful if you can't get the IDs of the rows with duplicate values? Yes, you can do a new query matching for each duplicate value, but is it possible to simply list the duplicates? – Muncey

@Muncey You can do a GROUP_CONCAT(id) and it will list the IDs. See my answer for an example. – Sheff

Thanks @MattRardon. A co-worker showed me that command a month or so ago, but I'm glad it's listed here because otherwise a lot of these queries aren't very useful. – Muncey

Is it possible to do something like this, but in a subquery that returns only one column? – Royer

What would it mean if it said ERROR: column "c" does not exist LINE 1? – Omaromara

Multiple columns can be done as follows: SELECT col1, col2, COUNT(*) c FROM table GROUP BY col1, col2 HAVING c > 1; – Amplify

I'm confused why this is the accepted answer and why it has so many upvotes. The OP asked, "I would like to find all the records that have duplicate values in this column." This answer returns a table of counts. -1 – Scrawly

For those that don't understand how HAVING works - it's simply a filter on the result set, so happens after the main query. – Catena

I find using having count(*) > 1 more intuitive – Bamboozle

I have a similar situation but I want to fetch the row containing second duplicate value of a single column, can you help me out here – Keim

yea ive been using this... it return the duplicated value and how many it has in that column. then i just copy the result and query separately for delete – Rorry

I added COLLATE 'utf8_bin' after the GROUP BY name to find only exact case matches in fields with utf8 collation. – Aqueduct

Answer 2

SELECT varchar_col
FROM table
GROUP BY varchar_col
HAVING COUNT(*) > 1;

Moneybag

Superior to @levik's answer since it doesn't add an extra column. Makes it useful for use with IN()/NOT IN(). – Paternity

This answer is exactly the same as levik's answer, just written differently as IDs of duplicate values are still omitted from the result. levik's answer just uses an alias of the count and this one does not. Perhaps this one is a bit cleaner if you don't need the duplicate count. – Finnic

Answer 3

SELECT  *
FROM    mytable mto
WHERE   EXISTS
        (
        SELECT  1
        FROM    mytable mti
        WHERE   mti.varchar_column = mto.varchar_column
        LIMIT 1, 1
        )
ORDER BY varchar_column

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 "return the second row"). 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.

Slender

Very good. I added ORDER BY varchar_column DESC to the end of query. – Emmer

This should be the accepted answer, as GROUP BY and HAVING returns only one of the possible duplicates. Also, performance with indexed field instead of COUNT(*), and the possibility to ORDER BY to group duplicate records. – Grimsley

As stated in the comments above, this query allows you to list all duplicated rows. Very useful. – Occupancy

Looking at this I don't understand how it would work at all. Wont the inner condition always be true since any row in the outer table will also be available in the inner table and so every row will always at least match itself? I tried the query and got the result i suspected - every row returned. But with so many upvotes I'm doubting myself. Isn't the inner query missing something like "AND mto.id<>mti.id"? It does work for me when I add that. – Minh

@clox: note the LIMIT 1, 1 at the bottom of the correlated query (essentially meaning "return the second row"). 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) – Slender

@Slender Ah, right. I understand the reasoning then, didn't pay attention to that part. However like I said, it doesn't work for me. It actually returns all rows. Even if I do LIMIT 1554554,1 it still does. but if I use the inner query by itself it works as expected and only returns rows with duplicate values. It does however work as you describe if I add ORDER BY to the inner query, and makes it a tad slower than with the condition. Are you sure it works for you without that order or extra inner condition? I'm using MariaDB by the way, can that be why? 10.0.26-MariaDB-3+deb.sury.org~xenial+1 – Minh

@Clox: it definitely did work back in 2009 on whatever was the most recent MySQL version back then, otherwise I would not have posted it. Could you please put an sqlfiddle together? – Slender

@Slender Alright. I've tried putting it on sqlfiddle but I've given up since every query I try to run, apart from creating the schema gets timed out. I did figure out that just removing "EXISTS" also makes the query work correctly for me. – Minh

Without analysis, this query took really long for a quite small table of a few thousand indexed rows. It was half a second to a second delay. The count query from the main answer was solved without noticeable delay (and it was done first, so no query cache) – Enshroud

This subquery looks equivalent, but easier to understand: https://mcmap.net/q/54545/-select-and-display-only-duplicate-records-in-mysql. Not sure which is faster. – Codfish

In mysql this query returned all results for me. I changed the WHERE statement to EXISTS ( ... ) > 0 to return only the duplicates. – Enticement

Answer 4

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

Sheff

Really appreciated Matt. This is truly helpful! For those trying to update in phpmyadmin if you leave the id together with the function like this: SELECT id, GROUP_CONCAT(id), name, COUNT(*) c [...] it enables inline editing and it should update all the rows involved (or at least the first one matched), but unfortunately the edit generates a Javascript error... – Selah

How would you then calculate how many ids are subject to duplication? – Boigie

How do I not get all the ID's grouped, but instead listed from first to last; with all their respective values in the columns next to them? So instead of grouping it, it just shows ID 1 and its value, ID 2 and its value. EVEN if the values for the ID is the same. – Leacock

Answer 5

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

Cyndi

This one shows each duplicate in it's own row. That's what I need. Thanks. – Dorking

Yes, exactly what I was looking for also. Displays all data and columns in rows which have a duplicate in a specific column. – Scavenge

Answer 6

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 <> b.Key

The advantage of this approach over the above answer is it gives the Key.

Christy

+1 Because it's handy. Though, ironically, the result itself contains duplicates (it lists a and b, then b and a.) – Underhill

@FabienSnauwaert You can get rid of some of the duplicates by comparing less than (or greater than) – Barimah

@Christy your answer is very clear, thanks for that but on large table it takes some time (about 2mn on more 20'000 entries table) and after show 25 first results, if I click to show next one, phpmyadmin show error "#1052 - Column 'id' in order clause is ambiguous" – Preposition

Answer 7

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(*) > 1
   ) ORDER BY field

Talich

Answer 8

SELECT * 
FROM `dps` 
WHERE pid IN (SELECT pid FROM `dps` GROUP BY pid HAVING COUNT(pid)>1)

Mesolithic

No, because this is quite possibly the slowest of the lot. Subselects are notoriously slow, as they're executed for every row returned. – Sybilsybila

Answer 9

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(*)>1;

Mesoglea

Answer 10

My final query incorporated a few of the answers here that helped - combining group by, count & GROUP_CONCAT.

SELECT GROUP_CONCAT(id), `magento_simple`, COUNT(*) c 
FROM product_variant 
GROUP BY `magento_simple` HAVING c > 1;

This provides the id of both examples (comma separated), the barcode I needed, and how many duplicates.

Change table and columns accordingly.

Amygdala

Answer 11

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

Greenlee

FYI - You'll want to 'select distinct somecol ..' if there is a potential for more than 1 duplicate record to exist otherwise the results will contain duplicates of the duplicated rows that were found. – Clio

Answer 12

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>1 (This query will SHOW the USER list which ARE greater THAN 1
              AND also COUNT)

Haggadist

Exactly what was needed! Here my query, checking 3 fields for duplicates: SELECT COUNT(CONCAT(userid,event,datetime)) AS total, userid, event, datetime FROM mytable GROUP BY CONCAT(userid, event, datetime ) HAVING total>1 – Topic

Answer 13

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

DB Fiddle Demo

Bistoury

Answer 14

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

Kosse

Answer 15

SELECT ColumnA, COUNT( * )
FROM Table
GROUP BY ColumnA
HAVING COUNT( * ) > 1

Flocculate

This is incorrect as it also finds unique occurrences. 0 should be 1. – Firenze

No idea why this is so low, the simplest answer that worked for me by far. I still find it crazy that something as basic as identifying duplicates is a four-line command in SQL, but that's better than some of the convoluted 10-line answers that were somehow voted higher than this one. – Iggy

Answer 16

I improved from this:

SELECT 
    col, 
    COUNT(col)
FROM
    table_name
GROUP BY col
HAVING COUNT(col) > 1; 

Phenolphthalein

Answer 17

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(*) > 1)

Repletion

Answer 18

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

Endpaper

Doing the same subquery twice seems inefficient. – Muncey

Answer 19

CREATE TABLE tbl_master
    (`id` int, `email` varchar(15));

INSERT INTO tbl_master
    (`id`, `email`) VALUES
    (1, '[email protected]'),
    (2, '[email protected]'),
    (3, '[email protected]'),
    (4, '[email protected]'),
    (5, '[email protected]');

QUERY : SELECT id, email FROM tbl_master
WHERE email IN (SELECT email FROM tbl_master GROUP BY email HAVING COUNT(id) > 1)

Savoirvivre

Answer 20

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

Code taken from : http://chandreshrana.blogspot.in/2014/12/find-duplicate-records-based-on-any.html

Hint

Answer 21

SELECT DISTINCT a.email FROM `users` a LEFT JOIN `users` b ON a.email = b.email WHERE a.id != b.id;

Sawfish

Worth noting that this is unbearably slow or might not even finish if the column being queried for is not indexed. Otherwise, I was able to change a.email to a.* and get all the IDs of the rows with duplicates. – Muncey

@Muncey What are you talking about? – Barimah

@Barimah Well since this is three years old I can't test on whatever version of MySQL I was using, but I tried this same query on a database where the column I selected did not have an index on it, so it took quite a few seconds to finish. Changing it to SELECT DISTINCT a.* resolved almost instantly. – Muncey

@Muncey Ah ok. I can understand it being slow... the part that I am concerned about is "might not even finish". – Barimah

@Barimah I don't remember which table in our system I had to run this query on, but for the ones with a few million records they probably would have finished, but in a time that took so long that I gave up on seeing when it actually would finish. – Muncey

Answer 22

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

Verleneverlie

Answer 23

Select column_name, column_name1,column_name2, count(1) as temp from table_name group by column_name having temp > 1

Polygnotus

Answer 24

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;

Signal

can you also add an explanation? – Hereunder

Why not use CREATE TEMPORARY TABLE ...? A little explanation of your solution would be great. – Valerie

Answer 25

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

Blowpipe

Answer 26

Try using this query:

SELECT name, COUNT(*) value_count FROM company_master GROUP BY name HAVING value_count > 1;

Peculate

Answer 27

Thanks to @novocaine for his great answer and his solution worked for me. I altered it slightly to include a percentage of the recurring values, which was needed in my case. Below is the altered version. It reduces the percentage to two decimal places. If you change the ,2 to 0, it will display no decimals, and to 1, then it will display one decimal place, and so on.

SELECT GROUP_CONCAT(id), name, COUNT(*) c, 
COUNT(*) OVER() AS totalRecords, 
CONCAT(FORMAT(COUNT(*)/COUNT(*) OVER()*100,2),'%') as recurringPecentage
FROM table
GROUP BY name
HAVING c > 1

Helban