Retrieve an arbitrary number of random rows from each 'group by' category in MySQL

Question

I have a table in MySQL that contains a column name category. I am trying to write a query that will return 2 random records from each category.

Here is code that I use to get 2 records from each category with the highest values in my rating column:

SELECT e1.* 
FROM   entries AS e1 
WHERE  (SELECT Count(*) 
        FROM   entries AS e2 
        WHERE  e2.category = e1.category 
               AND e1.rating <= e2.rating) <= 2 
ORDER  BY category, 
          rating DESC 

Check this link out to see a table with some sample data and the above query: http://sqlfiddle.com/#!9/bab8e/1

Answer 1

I achieved your desired results by sorting the table by the category column and a random number. I then assigned an incrementing number to each row that starts over each at 1 each time the category changed. I then return only the results that have a rowNum that is less than or equal to 2. If you wanted to return 3 random rows you would just change it to less than or equal to 3 and so on.

SELECT entry_id, 
       category, 
       rating 
FROM   (SELECT @currank := IF(@category = category, @currank + 1, 1) AS rowNum, 
               @category := category                                 AS categoryVar, 
               e1.* 
        FROM   (SELECT *, 
                       (SELECT @currank := 0) r 
                FROM   entries 
                ORDER  BY category, 
                          Rand()) AS e1)AS e2 
WHERE  rownum <= 2 
ORDER  BY category, 
          rating; 

Here is an sqlfiddle link like the one you posted in your question: http://sqlfiddle.com/#!9/bab8e/37/0

---

Do note that this same query could easily be adjusted to return a set number of records that are not random. For example, if you wanted to return the top 5 ratings from each category you could change the

ORDER BY category,rand()

to

ORDER BY category, rating DESC

and changing

WHERE rownum <= 2
to

WHERE rownum <= 5