SQL Sum top 5 rows for each team

Question

I got help with this SQL-question below to sum up top 5 lenght for each team. The problem for me now seems that SQL is randomly selecting 5 of the for eg 7 rows in the database for team 25, not the top 5. Anyone has any ideas for me? Would be much appriciated!

select team, sum(length) as totalScore 
from 
(
    SELECT
        t.*,
        @num_in_group:=case when @team!=team then @num_in_group:=0 else @num_in_group:=@num_in_group+1 end as num_in_group,
        @team:=team as t
    FROM reg_catches t, (select @team:=-1, @num_in_group:=0) init
    ORDER BY team asc
) sub
WHERE
    sub.num_in_group <= 4 and
    competition = 16    and
    team = 25 
GROUP BY team
ORDER BY totalScore DESC;

Table

team length competition
----------------------
26   70       16
25   70       16
25   95       16
25   98       16
25   100      16
25   100      16
25   100      16
25   122      16

Wanted output:

team totalScore
---- -----------
25  520
26  70

Best regards, Christian

Answer 1

If you wanted to get the top 5 rows for each team, you could utilise a parameter to rank each team's results:

SELECT *, 
(@rank := if(@team = team, @rank + 1, if(@team := team, 1, 1))) as rank 
FROM (SELECT * from reg_catches order by team, length desc) ordered
HAVING rank <= 5
ORDER BY team, rank asc

Which would produce the following: http://sqlfiddle.com/#!9/6042ae/1

| team | length | competition | rank |
+------+--------+-------------+------+
|  25  |   99   |     16      |   1  |
|  25  |   98   |     16      |   2  |
|  25  |   77   |     16      |   3  |
|  25  |   76   |     16      |   4  |
|  25  |   73   |     16      |   5  |
|  26  |   96   |     16      |   1  |
|  26  |   88   |     16      |   2  |
|  26  |   87   |     16      |   3  |
|  26  |   83   |     16      |   4  |
|  26  |   79   |     16      |   5  |

You could then, if required, wrap that query in a simple aggregation/group to get the total of the top 5 for each team

SELECT team, sum(length) as 'total score' FROM
(SELECT *, 
(@rank := if(@team = team, @rank + 1, if(@team := team, 1, 1))) as rank 
FROM (SELECT * from reg_catches order by team, length desc) ordered
HAVING rank <= 5
ORDER BY team, rank asc) top5
GROUP BY team

As per http://sqlfiddle.com/#!9/6042ae/2

| team  |total score|
+-------+-----------+
|  25   |   423     |
|  26   |   433     |
|  27   |   426     |

The usual caveats will apply to parameters, but you can't really 'rank' in MySQL without them. I also found this very sensitive to initially getting the scores in the right order before applying the parameters.

Whilst this might not be the most polished solution, I hope it helps