How to select 10 random rows(records) for each user_id column value in SQL?

Question

I have a database and would like to select 10 random rows for each User_ID.

So far I have this code, but I'm not sure how to have it select 10 random rows for each User_ID value.

SELECT User_ID 
FROM Database 
ORDER BY RAND() 
LIMIT 10 
GROUP BY User_ID

Possible duplicate of [MySQL select 10 random rows from 600K rows fast](https://stackoverflow.com/questions/4329396/mysql-select-10-random-rows-from-600k-rows-fast) — Derviş Kayımbaşıoğlu, Dec 11 '18 at 23:12

score 1 · Answer 1 · answered Dec 11 '18 at 23:32

This is a pain in MySQL -- prior to 8.0. Here is one method:

select d.*
from (select d.*,
             (@rn := if(@u = user_id, @rn + 1,
                        if(@u := user_id, 1, 1)
                       )
             ) as rn
      from (select d.* from database d order by user_id, rand()
           ) d cross join
           (select @u := 0, @rn := 0) params
     ) d
where rn <= 10;

How to select 10 random rows(records) for each user_id column value in SQL?

1 Answers1