Reputation: 63
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.
Upvotes: 4
Views: 4696
Reputation: 648
This doesn't work in MySQL, but in PostgreSQL you can use partition by
select name,report_id from
(select name,report_id,row_number()
over
(partition by name order by random())
as rn from Table) a
where rn<=10
I had this same question and found this answer from a colleague.
Upvotes: 1
Reputation: 24144
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
Upvotes: 5
Reputation: 33391
Try this:
SELECT
id,
name,
report_id
FROM
(
SELECT id,
report_id,
name,
CASE WHEN @name != name THEN @rn := 1 ELSE @rn := @rn + 1 END rn,
@name:=name
FROM (SELECT * FROM tbl ORDER BY RAND()) a,
(SELECT @rn:=0, @name := NULL) r
ORDER BY name
) s
WHERE rn <= 10;
Upvotes: 3