MySQL selects the top X entries for each person in the table

Question

Is there a better way to get multiple "top X" results from a MySQL table? I can do this easily with concatenation 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 add unions for each foo value. However, this is impractical when there are more than 500 different values ​​for foo, and I need the top X of each of them.

Answer 1

This type of query can be rephrased in the sense of "highest-n-per-group", where you want the top 10 points for a "group" to be "foo"

I suggest you take a look at this link , which deals with this question surprisingly, starting with the fact that it makes sense to fulfill your request and gradually optimize 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 want to do this at all levels of foo (i.e. imagine GROUP BY foo ), you can omit the line where foo in ...

Basically, an internal query ( SELECT foo, score FROM tablebar WHERE foo IN ('abc','def') ORDER BY foo, score DESC ) grabs foo and score from the table, first ordering foo and then evaluating the decrease.

@num := ... just increments each line, resetting to 1 for each new value of foo . That is, @num is just a line number / rank (try running an internal query yourself to see what I mean).

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

Note:

Duplicates are removed in the original query with UNION , so if the top 10 points for foo='abc' are 100, then only one row is returned (since the pair (foo,score) replicated 10 times). This will return duplicates.