By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE IF NOT EXISTS t (
ID int NOT NULL AUTO_INCREMENT,
Player int,
EventID int,
Points int,
PRIMARY KEY (ID),
UNIQUE KEY Player_EventID (Player,EventID)
);
INSERT INTO t (id, Player, EventID, Points) VALUES
(1, 1, 1, 25),
(2, 2, 1, 19),
(3, 1, 2, 25),
(4, 2, 2, 19),
(5, 1, 3, 20),
(6, 2, 3, 19),
(7, 1, 4, 20),
(8, 2, 4, 20);
SELECT Player
, MIN(CASE WHEN Eventid = 1 THEN points END) AS Event1
, MIN(CASE WHEN Eventid = 2 THEN points END) AS Event2
, MIN(CASE WHEN Eventid = 3 THEN points END) AS Event3
, MIN(CASE WHEN Eventid = 4 THEN points END) AS Event4
, SUM(CASE WHEN (Points, ID) >= (
SELECT Points, ID
FROM t AS x
WHERE x.Player = t.Player
ORDER BY Points DESC, ID DESC
LIMIT 2, 1
) THEN Points END) AS TopThree
FROM t
GROUP BY Player
| Player | Event1 | Event2 | Event3 | Event4 | TopThree |
|---|---|---|---|---|---|
| 1 | 25 | 25 | 20 | 20 | 70 |
| 2 | 19 | 19 | 19 | 20 | 58 |