MySQL Multiple Duplicate Rows - Return Only One

Question

I imported my iTunes Library into a table via a script. The script doesn't catch any duplicate song title and artists. Therefore, I have many duplicate songs.

trackid    song    artist   tracknum
4628       Title1  StackO      2
6846       YMCA    (blank)     11
9043       YMCA    (blank)     11
9381       YMCA    (blank)     11
9382       Title2  StackO      3

How would I write my SQL statement to return only the FIRST "trackid" of YMCA (4628) row when the song, artist and tracknum are all the same? It should look like this....

trackid    song    artist   tracknum
4628       Title1  StackO      2
6846       YMCA    (blank)     11
9382       Title2  StackO      3

Here is my SQL as it stands right now...

"SELECT DISTINCT * FROM wp_tracks WHERE title LIKE '%" . $song . "%' AND artist LIKE '%" . $artist . "%'

This has been baffling me for awhile now.

Thank you.

@u_mulder - I also need a trackid to be returned. That would just return it once, but as I stated in my question, I need the first result returned, that way I have the song, artist and trackid — Turp
– Turp, Commented Oct 14, 2013 at 17:52
You should clarify what you mean by FIRST because that implies ordering and you haven't explicitly said what order you want... so FIRST and LAST don't actually make any sense — Mosty Mostacho
– Mosty Mostacho, Commented Oct 14, 2013 at 18:00

AgRizzo · Accepted Answer · 2013-10-14 17:52:45Z

5

SELECT song, artist, tracknum, MIN(trackid) AS first_track_id
FROM wp_tracks
GROUP BY song, artist, tracknum

Add a WHERE clause to choose which tracks you want listed.

answered Oct 14, 2013 at 17:52

AgRizzo

5,2711 gold badge15 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

AlexP Over a year ago

+1 Perhaps a ORDER BY trackid ASC as well to cover 'return only the FIRST "trackid" of YMCA (4628)'

Mosty Mostacho Over a year ago

@AlexP Actually, the OP hasn't clarified whether FIRST means MIN in this context

Mosty Mostacho Over a year ago

@AlexP The fact that the data happens to match a MIN doesn't need to mean it is really expecting a MIN. IMO, it is most likely that the OP is just wanting to remove duplicates no matter the trackid that's why he mentions the first. So it is most likely that the aggregate function isn't even necessary and just leave the trackid alone in the select to improve query efficiency.

Mosty Mostacho · Accepted Answer · 2013-10-14 18:20:03Z

0

You have to mention what column you want to be DISTINCT, in your case song

SELECT DISTINCT song FROM wp_tracks
WHERE title LIKE '%" . $song . "%' AND artist LIKE '%" . $artist . "%

edited Oct 14, 2013 at 18:20

Mosty Mostacho

43.6k16 gold badges99 silver badges124 bronze badges

answered Oct 14, 2013 at 17:51

Krimson

7,73911 gold badges65 silver badges104 bronze badges

1 Comment

Mosty Mostacho Over a year ago

This doesn't answer the question. It is missing columns.

Collectives™ on Stack Overflow

MySQL Multiple Duplicate Rows - Return Only One

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related