sqlsql-serversql-server-2022

SQL Server random selection


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

Solution

  • 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.