CODEWITHSUNDEEP
mysqlsqlrandomgroup-bygreatest-n-per-group
Toniq
Toniq

Reputation: 5006

Mysql select random rows with multiple where conditions

This is my table example:

id     options     playlist_id
1      ...         7
3      ...         7
4      ...         9
11     ...         9
12     ...         7
14     ...         9

How do I select (for example) 3 random rows for each playlist_id and group results by playlist_id?

select id, options
from table
where playlist_id = 7 AND playlist_id = 9
group by playlist_id 
ORDER BY RAND()
LIMIT 3

I expect to return 3 random rows with playlist_id = 7 and 3 random rows with playlist_id = 9

Upvotes: 2

Views: 485

Answers (1)

GMB
GMB

Reputation: 222482

You need the 3 random songs per playlist. order by and limit alone cannot do that, since they operate on the whole resultset, while you somehow need to shuffle and limit within each group.

Assuming MySQL 8.0, you can use row_number() with an order by rand() clause; this assigns a random rank to each record within groups of records sharing the same playlist, that you can then use for filtering:

select id, options
from (
    select 
        t.*, 
        row_number() over(partition by playlis_id order by rand()) rn
    from mytable t
    where playlist_id in (7, 9)
) t
where rn <= 3

Upvotes: 2

Related Questions