greatest n per group - mysql select top n max values -

how can select top n max values table?

for table this:

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

for n=2, result needs be:

3     4     7     8     

the approach below selects max value each group.

select max(column1) table group column2 

returns:

4 8 

for n=2 could

select max(column1) m  table t group column2 union select max(column1) m table t column1 not in (select max(column1)                        column2 = t.column2) 

for n use approaches described here simulate rank on partition.

edit: this article give need.

basically this

select t.*    (select grouper,           (select val             table li            li.grouper = dlo.grouper            order                  li.grouper, li.val desc            limit 2,1) mid           (       select distinct grouper       table       ) dlo     ) lo, table t t.grouper = lo.grouper       , t.val > lo.mid 

replace grouper name of column want group , val name of column hold values.

to work out how functions go step-by-step inner query , run them.

also, there slight simplification - subquery finds mid can return null if category not have enough values there should coalesce of constant make sense in comparison (in case min of domain of val, in article max).

edit2: forgot mention limit 2,1 determines n (limit n,1).