greatest n per group - MySQL select top X records for each individual in table

Question

Is there a better way to get multiple "top X" results from a MySQL table? I'm able to accomplish this easily with a union when the number of different foo is small:

(SELECT foo,score FROM tablebar WHERE (foo = 'abc') ORDER BY score DESC LIMIT 10) 
UNION 
(SELECT foo,score FROM tablebar WHERE (foo = 'def') ORDER BY score DESC LIMIT 10)

I could obviously keep adding unions for each value of foo. However, this isn't practical when there are 500+ different values for foo and I need the top X of each.

Answer 1

This sort of query can be rephrased in a "greatest-n-per-group" sense, where you want the top 10 scores per "group" being values of 'foo'.

I suggest you have a look at this link that deals with this question marvellously, starting off with a way that makes sense to perform your query and gradually optimising it.

set @num := 0, @foo := '';
select foo, score
from (
   select foo, score,
      @num := if(@foo = foo, @num + 1, 1) as row_number,
      @foo := foo as dummy
  from tablebar
  where foo IN ('abc','def')
  order by foo, score DESC     
) as x where x.row_number <= 10;

If you wanted to perform this across all levels of foo (i.e. imagine doing a GROUP BY foo), you can omit the where foo in ... line.

Basically the inner query (SELECT foo, score FROM tablebar WHERE foo IN ('abc','def') ORDER BY foo, score DESC) grabs foo and score from the table, ordering first by foo and then score descending.

The @num := ... just increases every row, resetting to 1 for each new value of foo. That is, @num is just a row number/rank (try running the inner query on its own to see what I mean).

The outer query then selects rows where the rank/row number is less than or equal to 10.

NOTE:

Your original query with UNION removes duplicates, so if the top 10 scores for foo='abc' are all 100 then only one row will be returned (since the (foo,score) pair is replicated 10 times). This one will return duplicates.