Franco Franco - 6 months ago 10
SQL Question

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

Answer

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