'Find duplicates in the same table in MySQL

I have a table with two columns - artist, release_id

What query can I run to show duplicate records?

e.g. my table is

ArtistX : 45677
ArtistY : 378798
ArtistX : 45677
ArtistZ : 123456
ArtistY : 888888
ArtistX : 2312
ArtistY: 378798

The query should show

ArtistX : 45677
ArtistX : 45677
ArtistY : 378798
ArtistY : 378798
mysql


Solution 1:[1]

You can use a grouping across the columns of interest to work out if there are duplicates.

SELECT
    artist, release_id, count(*) no_of_records
FROM table
GROUP BY artist, release_id
HAVING count(*) > 1;

Solution 2:[2]

SELECT id,artist,COUNT(*) FROM myTable
GROUP BY artist, release_id HAVING COUNT(*) > 1

Solution 3:[3]

you can try something like this

select artist, count(*) from mytable group by artist having count(*) > 1;

wich would output

artist   count(*)
45677    2
378798   2

Solution 4:[4]

SELECT
    artist, release_id, count(*) no_of_records, group_concat(id)
FROM table
GROUP BY artist, release_id
HAVING count(*) > 1;

also adding group_concat(id) gets you all ids of the duplicates.

Solution 5:[5]

SELECT row, COUNT(row) AS num FROM mytable GROUP BY row HAVING (num > 1);

Solution 6:[6]

select * from table where artist IN (select artist from table group by artist having count(ID)>1) and release_id IN (select release_id from table group by release_id having count(release_id)>1);

Will Fetch:

ArtistX : 45677
ArtistX : 45677
ArtistY : 378798
ArtistY : 378798

Solution 7:[7]

you can use this query for the same result. it works for me

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

Solution 8:[8]

SELECT id,artist,COUNT(id) as found FROM table GROUP by id HAVING found > 1

Solution 9:[9]

SELECT artist, count(*) 
FROM tableName 
GROUP BY artist 
HAVING count(*) > 1;

Solution 10:[10]

Try this:

SELECT A.ARTIST,A.RELEASE_ID FROM ARTISTS A
WHERE EXISTS(
SELECT 'X' FROM ARTISTS B
WHERE B.ARTIST = A.ARTIST AND B.RELEASE_ID = A.RELEASE_ID
GROUP BY B.ARTIST,B.RELEASE_ID
HAVING COUNT(B.ARTIST)>1)
ORDER BY A.ARTIST;

Solution 11:[11]

This method might not be great for you, but if you ever want to get rid of duplicates and do this while making sure they are duplicates indeed you can try this:

  1. duplicate your table1 into table2, for example like this:

    CREATE TABLE table2 AS SELECT * FROM table1;

  2. add a new column to table1, for example, name it to count

  3. run a query (this assumes release_id should an unique column):

UPDATE table1 AS t1 SET t1.kount = (SELECT COUNT(*) FROM table2 AS t2 WHERE t1.release_id = t2.release_id)

  1. drop table table2

  2. use table1.kount to find your duplicates and remove them or something. Preferably in PHP/Python/Perl. This way you can, for example, make sure they are indeed duplicates and just have the same release_id. The same release_id might be given by accident and titles, years of publication, etc. might be different. So just put your code here to filter the duplicates (pseudocode):

foreach (sql(SELECT * FROM table1 WHERE kount>1)) do
    //do something

Solution 12:[12]

You can also try something like this:

SELECT W.artist, W.release_id FROM table W, table W1 
WHERE W.artist= W1.artist AND W.release_id = W1.release_id;

Solution 13:[13]

If you have more unique column in one row, you can use this:

DELETE FROM table WHERE id in(
    SELECT x.id 
    FROM ( 
        SELECT *,count(id) cc FROM table group by col1,col2,col3... 
    ) x
    WHERE x.cc>1
)

Related Questions