MySQL - Select Top 5 with Rankings

Question

I'm trying to get a users ranking getting his highest performances in every beatmap.

I get the user highest performance in every beatmap (only taking the top 5 performances) and adding them together, but it fails when the highest performance in one beatmap is repeated... because it counts twice

I'm based in this solution, but it doesn't works well for me...

Using MySQL 5.7

What i'm doing wrong?

Fiddle

Using this code:

SET group_concat_max_len := 1000000;

SELECT @i:=@i+1 rank, x.userID, x.totalperformance FROM (SELECT r.userID, SUM(r.performance) as totalperformance 
FROM 
(SELECT Rankings.*
FROM   Rankings INNER JOIN (
  SELECT   userID, GROUP_CONCAT(performance ORDER BY performance DESC) grouped_performance
  FROM     Rankings
  GROUP BY userID) group_max
  ON Rankings.userID = group_max.userID
     AND FIND_IN_SET(performance, grouped_performance) <= 5

ORDER BY
  Rankings.userID, Rankings.performance DESC) as r
  GROUP BY userID) x
  JOIN 
     (SELECT @i:=0) vars
 ORDER BY x.totalperformance DESC

Expected result:

+------+--------+------------------+
| rank | userID | totalperformance |
+------+--------+------------------+
| 1    | 1      | 450              |
+------+--------+------------------+
| 2    | 2      | 250              |
+------+--------+------------------+
| 3    | 5      | 140              |
+------+--------+------------------+
| 4    | 3      | 50               |
+------+--------+------------------+
| 5    | 75     | 10               | 
+------+--------+------------------+
| 6    | 45     | 0                | --
+------+--------+------------------+
| 7    | 70     | 0                | ----> This order is not relevant
+------+--------+------------------+
| 8    | 76     | 0                | --
+------+--------+------------------+

Actual Result:

+------+--------+------------------+
| rank | userID | totalperformance |
+------+--------+------------------+
| 1    | 1      | 520              |
+------+--------+------------------+
| 2    | 2      | 350              |
+------+--------+------------------+
| 3    | 5      | 220              |
+------+--------+------------------+
| 4    | 3      | 100              |
+------+--------+------------------+
| 5    | 75     | 10               |
+------+--------+------------------+
| 6    | 45     | 0                | --
+------+--------+------------------+
| 7    | 70     | 0                | ----> This order is not relevant
+------+--------+------------------+
| 8    | 76     | 0                | --
+------+--------+------------------+

Answer 1

As you have mentioned that you are picking only top 5 performances per user across beatmaps then you can try this way:

select @i:=@i+1, userid,performance from (
select userid,sum(performance) as performance from (
select 
  @row_number := CASE WHEN @last_category <> t1.userID THEN 1 ELSE @row_number + 1 END AS row_number,
  @last_category :=t1.userid,
t1.userid,
t1.beatmapid,
t1.performance

from (
select 
 userid, beatmapid,
 max(performance) as performance
from Rankings 
group by userid, beatmapid
) t1 
CROSS JOIN (SELECT @row_number := 0, @last_category := null) t2
ORDER BY t1.userID , t1.performance desc
) t3
where row_number<=5
group by userid
)
t4 join  (SELECT @i := 0 ) t5
order by performance desc

Above query will not consider duplicate Performance Score and pick only top 5 performance values.

DEMO