Find duplicates in the same table in MySQL

Question

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