mysql - select random rows with max 3 rows per parent id I currently have a query that returns a maximum of 3 rows per parent_id. I want to enhance the query to return a maximum of 3 random rows per parent_id. This does not work for me at the moment as it returns to me Ascending id's each time I try.

SELECT id, rest_id, rn
FROM (SELECT (@rn := if(@rest_id = rest_id, @rn + 1,
                    if(@rest_id := rest_id, 1, 1)
                   )
         ) as rn,
         meals.*   
  FROM meals CROSS JOIN
       (SELECT @rn := 0, @rest_id := '') params
  ORDER BY rand() 
 ) meals
WHERE rn &lt;= 3
order by id ASC
If you want random rows per parent_id, your inner ORDER BY should arrange rows in a way that same parent_id rows are grouped together. So, instead of

ORDER BY rand()


it should be

ORDER BY parent_id ASC, rand()


As for this:


  it returns to me Ascending id's each time I try


The reason your query returns ascending IDs each time is because you have instructed it to return the results in the ascending order of id with this line at the end of the query:

ORDER BY id ASC


So, again, if you want the output to show same parent_id rows grouped together, add the parent_id column as the first sorting criterion to the final ORDER BY as well:

ORDER BY parent_id ASC, id ASC