Get an arbitrary number of random rows from each category "by group" in MySQL

Question

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

Here is the code I use to get 2 entries from each category with the highest values ​​in the 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 to see a table with some sample data and the above query: http://sqlfiddle.com/#!9/bab8e/1

Answer 1

I achieved the desired results by sorting the table by category column and random number. Then I assigned an incremental number to each line that starts with each of them, 1 each time the category changes. Then I return only those results that have a rowNum that is less than or equal to 2. If you want to return 3 random rows, you just change them 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 a sqlfiddle link similar to the one you posted in your question: http://sqlfiddle.com/#!9/bab8e/37/0

---

Please note that the same query can be easily configured to return a given number of records that are not random. For example, if you want to return the top 5 rating from each category , you can change

ORDER BY category,rand()

to

ORDER BY category, rating DESC

and changes

WHERE rownum <= 2
before

WHERE rownum <= 5