mysql - Select n random rows per specified group

Question

I am struggling to find an optimal solution for the following problem.

Suppose I have a table 'Table' like this:

id    name    report_id
 1    name1    1
 2    name2    3
 3    name3    5
 4    name1    7
 5    name3    8
 ....................

I want to select for each value in a set: ('name1', 'name2') 10 random unique rows.

Of course it is possible to do with union like:

(SELECT * FROM Table
WHERE
    name='name1'
ORDER BY RAND() LIMIT 10)
UNION
(SELECT * FROM Table
WHERE
    name='name2'
ORDER BY RAND() LIMIT 10)

But if I have 100 unique names for which I have to select 10 random records - this query is going to be a bit large.

Answer 1

SQLFiddle demo

select ID,NAME,REPORT_ID
from
(
select *, @row:=if(name=@name,@row,0)+1 as rn, @name:=name from 
(select *,RAND() as trand from t) t1,
(select @row:=0,@name:='') tm2 
order by name,trand
) t2
where rn<=10