Find duplicates in one table in MySQL

Question

I have a table with two columns - artist, release_id

What query can I run to display duplicate entries?

eg. my table

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

The request should show

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

Answer 1

You can use grouping by column of interest for development if there are duplicates.

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

Answer 2

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

Answer 3

you can try something like this

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

which outputs

 artist count(*) 45677 2 378798 2 

Answer 4

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

Answer 5

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) returns all duplicate identifiers.

Answer 6

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

SELECT name of the first, last, list.address FROM list INNER JOIN (SELECT address FROM list GROUP BY address HAVING count (id)> 1) dup ON list.address = dup.address

Answer 7

select * from the table, where artist IN (select the artist from the table group by the artist with the counter (ID)> 1) and release_id IN (select release_id from the group by release_id table with count (release_id)> 1);

Choose: ArtistX: 45677 ArtistX: 45677 Code number: 378798 Code number: 378798

Answer 8

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

Answer 9

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

Answer 10

Try the following:

 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; 

Answer 11

This method may be inconvenient for you, but if you ever want to get rid of duplicates and do it, making sure that they are duplicates, you can try the following:

• duplicate table1 in table2 , for example, as follows:

  CREATE TABLE table2 AS SELECT * FROM table1;

• add a new column to table1 e.g. name it kount

• run the query (this assumes release_id must have a unique column):

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

• drop table table2

• use table1 .kount to find duplicates and delete them or something else. Preferably in PHP / Python / Perl. Thus, you can, for example, make sure that they are really duplicates and have the same release_disk. The same release_ID can be assigned randomly, but names, years of publication, etc. May vary. So just put your code to filter duplicates (pseudo-code):

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

Answer 12

If you have a 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 )