mysql select top n max values

Question

How can you select the top n max values from a table?

For a table like this:

column1  column2
   1       foo
   2       foo
   3       foo
   4       foo
   5       bar
   6       bar
   7       bar
   8       bar

For n=2, the result needs to be:

3    
4    
7    
8    

The approach below selects only the max value for each group.

SELECT max(column1) FROM table GROUP BY column2

Returns:

4
8

Answer 1

For n=2 you could

SELECT max(column1) m 
FROM table t
GROUP BY column2
UNION
SELECT max(column1) m
FROM table t
WHERE column1 NOT IN (SELECT max(column1) 
                      WHERE column2 = t.column2)

for any n you could use approaches described here to simulate rank over partition.

EDIT: Actually this article will give you exactly what you need.

Basically it is something like this

SELECT t.*
FROM
   (SELECT grouper,
          (SELECT val 
           FROM table li
           WHERE li.grouper = dlo.grouper
           ORDER BY
                 li.grouper, li.val DESC
           LIMIT 2,1) AS mid
   FROM 
      (
      SELECT DISTINCT grouper
      FROM table
      ) dlo 
   ) lo, table t
WHERE t.grouper = lo.grouper
      AND t.val > lo.mid

Replace grouper with the name of the column you want to group by and val with the name of the column that hold the values.

To work out how exactly it functions go step-by-step from the most inner query and run them.

Also, there is a slight simplification - the subquery that finds the mid can return NULL if certain category does not have enough values so there should be COALESCE of that to some constant that would make sense in the comparison (in your case it would be MIN of domain of the val, in article it is MAX).

EDIT2: I forgot to mention that it is the LIMIT 2,1 that determines the n (LIMIT n,1).