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.
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.
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 . "%