How to get 2 random rows per user_id from a mySQL table? -

i have table has many products different user_id's. this:

product_id | user_id 01           20 02           20 03           20 04           20 05           32 06           32 07           53 08           53 09           53 10           84 11           84 etc. 

i'm trying 2 rows each different user_id, sorted randomly.

the closest got query:

select * db_products order user_id, rand()  

but gives me every row sorted randomly, instead of 2 rows each user_id sorted randomly.

i tried limit 2 shows 2 rows total. what's easiest way achieve this?

thank :)

the easiest way use variables:

select p.* (select p.*,              (@rn := if(@u = user_id, @rn + 1,                         if(@u := user_id, 1, 1)                        )              ) rn       db_products p cross join            (select @u := 0, @rn := 0) params       order user_id, rand()      ) p rn <= 2; 

there other possibilities. if strings unlikely overflow , want products on single row:

select user_id,        substring_index(group_concat(product_id order rand(), ',', 2) db_products group user_id;