I have a table which holds 70.000 rows for my MP3 files (1.200 artists; 6.500 albums). And I want to generate my specific playlist.
From each artist and from each album, I want to select only one track.
For example, all 17 albums I have from Queen should give met a list of 17 tracks.
This should give me data to build the playlist, but how to select?
SELECT DISTINCT
returns all albums from all artists.
But I want extra is 1 track selected, and in a random way, so the next time I execute the query I get a different list.
Table structure:
[dbo].[MP3Tracks]
[MP3_ID]
,[MP3_Path]
,[MP3_Artist]
,[MP3_Genre]
,[MP3_Album]
,[MP3_Year]
,[MP3_TrackNr]
,[MP3_Title]
I used SELECT DISTINCT
, SELECT {field} FROM [table} ORDER BY NEWID()
and combinations.
one sample row:
MP3_ID | MP3_Path | MP3_Artist | MP3_Genre | MP3_Album | MP3_Year | MP3_TrackNr | MP3_Title |
---|---|---|---|---|---|---|---|
47410 | Q:\C\Crosby & Nash\Crosby & Nash (1998) Another Stoney Evening\ | Crosby & Nash | C | Another Stoney Evening | 1998 | 15 | 15 - Guinevere |
There are many ways to go about this, but here is a very rudimentary solution.
;WITH x
AS
(
SELECT MP3_ID
, MP3_Path
, MP3_Artist
, MP3_Genre
, MP3_Album
, MP3_Year
, MP3_TrackNr
, MP3_Title
, ROW_NUMBER() OVER (PARTITION BY MP3_Artist, MP3_Album ORDER BY NEWID()) AS rn
FROM dbo.MP3Tracks
)
SELECT x.MP3_ID
, x.MP3_Path
, x.MP3_Artist
, x.MP3_Genre
, x.MP3_Album
, x.MP3_Year
, x.MP3_TrackNr
, x.MP3_Title
FROM x
WHERE x.rn = 1;
Use this dbFiddle for reference containing schema and sample data.