select top 5 scores for each team

Question

This is different from the one marked as a double, I want to sum up top 5 for each team. The double post takes out for each of the results in separate rows.

I'm using this question now but it seems that SQL is randomly returning 5 of for exasmple 10 rows and sum up, not the top 5. Anyone has some input for me?

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;

I'm struggeling on a SQL question that I can't get my head around. My result-table looks like below, I'm trying to sum up the top 5 result for each team and limit the output to the top 3 highest ranked teams. Everything was working as expected until I added my last score in the result-table. The output of my SQL now is randomly for team 25. I've expected that to be 520..

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

Output:

team totalScore
---- -----------
25  122
26  70

Wanted output:

team totalScore
---- -----------
25  522
26  70


SELECT team, SUM(length) AS totalScore
FROM(
  SELECT team, length
  FROM table_result m
  WHERE competition = 16 and (
    SELECT COUNT(*)
    FROM table_result mT
    WHERE mT.team = m.team AND mT.length >= m.length
    ) <= 5) tmp
GROUP BY team
ORDER BY totalScore DESC Limit 3

Anyone has any ideas for me?

This problem is made unnecessarily complicated by your failure to include a PRIMARY KEY. — Strawberry, Apr 12 '17 at 16:32

score 0 · Answer 1 · answered Apr 12 '17 at 16:25

You should use a window function to accomplish this. Here's an example query:

SELECT team, SUM(length) AS totalScore FROM
  (SELECT team,
          length,
          row_number() OVER (PARTITION BY team ORDER BY length desc) AS rowNumber
   FROM table_result) tmp
WHERE rowNumber <= 5
AND competition = 16
GROUP BY team
ORDER BY totalScore DESC
LIMIT 3;

This query has two parts.

The inner query uses the row_number() window function to give every row an extra column that indicates its rank. PARTITION BY team says that the rank should be kept separately for each team, so that you end up being able to select the top n scores for every team.

The outer query uses a GROUP BY on the result of the inner query to take the SUM, per team, of all of the scores whose row number is less than or equal to 5 - in other words, the top 5 scores.

Thanks for your help, as said above this doesn't work with my MariaDB, but the help is much appreciated anyway and hopefully it will help someone else! Best regards, Christian — Christian Åvall, Apr 12 '17 at 18:39

score 0 · Accepted Answer · answered Apr 12 '17 at 16:30

0

select team, sum(length) 
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 test.table_result t, (select @team:=-1, @num_in_group:=0) init
    ORDER BY team, length desc) sub
WHERE sub.num_in_group<=4
GROUP BY team

answered Apr 12 '17 at 16:30

StanislavL

56,971
9
68
98

This is just perfect! Thanks for your quick respons and the solution! – Christian Åvall Apr 12 '17 at 18:40
Hi! I have problem with this query now when we're live with the site.. :( It seems that the query returns random 5 results and sums up this, I would like it to sum up the top 5.. Anyone has any idea for me? – Christian Åvall Oct 07 '17 at 09:08

select top 5 scores for each team

2 Answers2