Search code examples
mysqlsqlgroup-bylimit

How to get random n records for each group in MySQL

I am trying to extract n random records for each group in my database.

User Data
user1 data_User1_1
user1 data_User1_2
user1 data_User1_3
user1 data_User1_4
user1 data_User1_5
user2 data_User2_1
user2 data_User2_2
user3 data_User3_1
user3 data_User3_2
user3 data_User3_3
user3 data_User3_4

And I am trying to retrieve, for example, two random rows for each user in my table:

User Data
user1 data_User1_1
user1 data_User1_3
user2 data_User2_1
user2 data_User2_2
user3 data_User3_1
user3 data_User3_4

I am new to SQL, therefore what I did for now is the following query for each user in my table, as there are not many for now. But I would like to automate this with only one query, if possible.

select data
from table 
where user = 'user1'
order by rand()
limit 2;
Solution

  • here is one way:

    select * from (
   select * , row_number() over (partition by user order by rand()) as rn
   from table
) t where rn <= 2