I\'ve got a table with many different items which are categorized into eight groups. An auxiliary table has counts of hits against each item. I\'d like to select the top 3 items in each group, i.e., a total of 24 rows. I know I can do this in a loop, e.g., (in pseudo-php-mysql)
for ($i = 1; $i <= 8; ++$i) select item, hitcount from hits where group = $i order by hits desc limit 0, 3;
I was wondering whether there was some way to do this in a single SQL invocation instead of eight passes. I\'m sort of familiar with the GROUP BY and HAVING clauses, but I\'m not sure they can be used to achieve this.