Finding the highest n values of each group in MySQL -

i have data formatted this:

lane         series 1            680 1            685 1            688 2            666 2            425 2            775 ... 

and i'd grab highest n series per lane (let's 2 sake of example, many more that)

so output should be:

lane         series 1            688 1            685 2            775 2            666 

getting highest series per lane easy, can't seem find way highest 2 results.

i use max aggregate function group max, there's no "top n" function in sql server , using order by... limit returns highest n results overall, not per lane.

since use java application coded myself query database , choose n is, loop , use limit , loop through every lane, making different query each time, want learn how using mysql.

this solution fastest mysql , work large tables, uses "funky" mysql features, wouldn't of use other database flavours.

(edited sort before applying logic)

set @count:=-1, @lane:=0;  select lane, series (select lane, series lane_series order lane, series desc) x if(lane != @lane, @count:=-1, 0) not null , if(lane != @lane, @lane:=lane, lane) not null , (@count:=@count+1) < 2; -- specify number of row @ top of each group here 

to put query on steroids, define index on lane , series: create index lane_series_idx on lane_series(lane, series); , (super fast) index-only scan - other text columns don't affect it.

good points of query are:

1. it requires 1 table pass (albeit sorted)
2. it handles ties @ level, example if there's tie 2nd, one of 2nd displayed - ie row count absolute , never exceeded

here's test output:

create table lane_series (lane int, series int);  insert lane_series values (1, 680),(1, 685),(1, 688),(2, 666),(2, 425),(2, 775);  -- execute above query:  +------+--------+ | lane | series | +------+--------+ |    1 |    688 | |    1 |    685 | |    2 |    775 | |    2 |    666 | +------+--------+