CODEWITHSUNDEEP
mysqlsqlgreatest-n-per-group
seanzxx
seanzxx

Reputation: 116

Finding the highest n values for each group

I happened to the same problem as this Finding the highest n values of each group in MySQL

I have some data like this:

+ --------- + ----------- +
| lane      | series      |
+ --------- + ----------- +
| 1         | 680         |
| 1         | 685         |
| 1         | 688         |
| 2         | 666         |
| 2         | 425         |
| 2         | 775         |
+ --------- + ----------- +

And I'd like to grab the highest n series per lane (let's say 2 for the sake of this example, but it could be many more than that) So the output should be:

+ --------- + ----------- +
| lane      | series      |
+ --------- + ----------- +
| 1         | 688         |
| 1         | 685         |
| 2         | 775         |
| 2         | 666         |
+ --------- + ----------- +

I think the SQL with "funky" MySQL features are quite good. 

set @count:=-1, @lane:=0; 
select lane, series
from (select lane, series from lane_series order by lane, series desc) x
where if(lane != @lane, @count:=-1, 0) is not null
and if(lane != @lane, @lane:=lane, lane) is not null
and (@count:=@count+1) < 2; -- Specify the number of row at top of each group here

To my surprise, it works very well on MySQL 5.0 and MySQL 5.1, but it doesn't work on MySQL 5.5 which is my production MySQL version.

The result on MySQL 5.5 is like this:

+ --------- + ----------- +
| lane      | series      |
+ --------- + ----------- +
| 1         | 688         |
| 1         | 685         |
+ --------- + ----------- +

Please help me to make it work on MySQL 5.5 or tell me why the result is different, thanks a lot!

UPDATE: Because there're about 3 million records in my production database, I'd like to know how to get the result in a fast way, as fast as possible.

Upvotes: 2

Views: 216

Answers (1)

John Woo
John Woo

Reputation: 263933

Try this one, maybe it will work for you.

SELECT lane, series
FROM   tableA
WHERE (
        SELECT count(*)
        FROM   tableA AS f
        WHERE  f.lane = tableA.lane AND 
               f.series >= tableA.series
      ) <= 2
ORDER BY lane, series DESC

SQLFiddle Demo

Upvotes: 5

Related Questions