0

I have a database of events. Each event has a rank attatched to it (1-5). I want to query the db for events by date range, I want to select a random set of event with rank 1, followed by a random set of of events with rank 2 etc.

This query gives me a random selection of rank 1 events:

SELECT * FROM
(
SELECT * FROM  `events` 
WHERE  `date` >1326578956 
AND `date`<1326870399 
AND `rank`=1 
ORDER BY RAND()
) 
e LIMIT 0,10

But there maybe cases where there are no events with rank 1, or rank 2 etc,.

Is this possible with only a MYSQL query. I could iterate the query using PHP, filling an array with results but I think it would be far more elegant to do it all with MYSQL if possible.

Thanks,

Alan.

Alan A
  • 2,557
  • 6
  • 32
  • 54
  • Probably relevant: http://stackoverflow.com/questions/12113699/get-top-n-records-for-each-group-of-grouped-results – Marc B Aug 01 '13 at 15:19
  • You don't need 2 selects in above query. Just limit the inner select and it will do the same. – Flash Thunder Aug 01 '13 at 15:21
  • @FlashThunder Yes I can see I don't need the 2 selectors, I was working on a solution similar to that which 'Tom Mac' gave below but forgot to remove the extra select.... – Alan A Aug 01 '13 at 16:44

1 Answers1

0

Give this a bash:

select *
from
(
SELECT * FROM  `events`  WHERE  `date` >1326578956  AND `date`<1326870399  AND `rank`=1  ORDER BY RAND() LIMIT 10
union all
SELECT * FROM  `events`  WHERE  `date` >1326578956  AND `date`<1326870399  AND `rank`=2  ORDER BY RAND() LIMIT 10
union all
SELECT * FROM  `events`  WHERE  `date` >1326578956  AND `date`<1326870399  AND `rank`=3  ORDER BY RAND() LIMIT 10
union all
SELECT * FROM  `events`  WHERE  `date` >1326578956  AND `date`<1326870399  AND `rank`=4  ORDER BY RAND() LIMIT 10
union all
SELECT * FROM  `events`  WHERE  `date` >1326578956  AND `date`<1326870399  AND `rank`=5  ORDER BY RAND() LIMIT 10
) e
order by e.rank
limit 10;
Tom Mac
  • 9,693
  • 3
  • 25
  • 35
  • thanks @TomMac that looks like what I was trying to do, I had planned this but wasn't sure of the "union all" syntax. I will test and let you know...! – Alan A Aug 01 '13 at 16:42
  • Please note that it will be slower than separate queries with PHP. Generally it is better idea to split queries not to merge. Union is extremely slow, especially on bigger data set. – Flash Thunder Aug 03 '13 at 13:07