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

what exactly you require..2 random records from each category right?? or something, does that related to query you have included..!? — Chella, Dec 14 '12 at 17:24
The query is just there to show what I'm working with, say that I have 30 entries in a table 10 of each category, I would like to retrieve 2 random entries of each of those categories. That would return 6 results. — Ivar, Dec 14 '12 at 17:31
Take a look at this post: http://stackoverflow.com/questions/984396/how-to-get-mysql-random-integer-range — ntgCleaner, Dec 14 '12 at 17:36

score 1 · Answer 1 · edited Jun 20 '20 at 09:12

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

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

1 Answers1