CODEWITHSUNDEEP
mysqljoingroup-by
Kilokahn
Kilokahn

Reputation: 147

MySQL select a random row per each grouped column value

I am using SAM Broadcaster which separates categories and the songs they contain.

What I want is a list of 5 random artists with a random selection of their songs, no repeat artist. This is what I have done so far:

I have a category named "Classics":

select ID from category where name = 'Classics';

This returns: 11

So with this I can get a list of all the items in the categorylist:

SELECT artist, title 
FROM songlist LEFT OUTER JOIN categorylist on songlist.id = categorylist.songID 
WHERE categorylist.categoryID = 11 ORDER BY RAND();

This returns:

Now I want to go 1 step further and group this list by artist and limit 5 BUT I want a random item of each of the titles. Right now when I group by artist I ALWAYS get the titles in ABC order. So Seals & Crofts' "Diamond Girl" is always listed when their name shows. Same with every other group.

The goal is to have a list of 5 random songs from this list with no repeat artists.

Upvotes: 3

Views: 462

Answers (1)

guido
guido

Reputation: 19194

This query should do the trick (intentionally leaving the category selection out of the way to make it more readable):

SELECT artist,
       title
FROM   (SELECT artist,
               title,
               CASE
                 WHEN @artist != artist THEN @rn := 1
                 ELSE @rn := @rn + 1
               end rn,
               @artist := artist
        FROM   (SELECT *
                FROM   songlist
                ORDER  BY Rand()) a,
               (SELECT @rn := 0,
                       @name := NULL) r
        ORDER  BY a.artist) s
WHERE  rn <= 1
ORDER  BY Rand()
LIMIT  5

Find a demo here: sqlfiddle

Upvotes: 2

Related Questions