CODEWITHSUNDEEP
mysql
Franco
Franco

Reputation: 2926

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

Upvotes: 28

Views: 59955

Answers (13)

dev009
dev009

Reputation: 765

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.

Upvotes: 10

Mahek Shah
Mahek Shah

Reputation: 1

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;

Upvotes: 0

Sarthak Y. Vaidya
Sarthak Y. Vaidya

Reputation: 31

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

Upvotes: 3

Shiva Kumar
Shiva Kumar

Reputation: 41

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

Upvotes: 4

Tom
Tom

Reputation: 3044

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

Upvotes: 0

Jenovai Matyas
Jenovai Matyas

Reputation: 69

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
)

Upvotes: -2

Jayy
Jayy

Reputation: 2436

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;

Upvotes: 0

John Woo
John Woo

Reputation: 263883

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

Upvotes: 1

Laurent Grégoire
Laurent Grégoire

Reputation: 4146

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

Upvotes: 2

a'r
a'r

Reputation: 37029

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;

Upvotes: 76

DonCallisto
DonCallisto

Reputation: 29932

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

Upvotes: 5

Stelian Matei
Stelian Matei

Reputation: 11623

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

Upvotes: 0

oezi
oezi

Reputation: 51817

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

Upvotes: 3

Related Questions