SQL - Max value of different sums

Question

So I'm still working on my Football manager archive. Here is the query that results in the total points for every season:

SELECT
p.username,
SUM(CASE WHEN m.season_id=4 THEN points END) as 'season14/15',
SUM(CASE WHEN m.season_id=5 THEN points END) as 'season15/16',
SUM(m.points) AS total_points 
FROM 
players p
INNER JOIN 
matchdays m ON p.userid = m.userid
GROUP BY p.userid
ORDER BY total_points DESC`

The result is:

+----------+-------------+-------------+--------------+
| username | season14/15 | season15/16 | total_points |
+----------+-------------+-------------+--------------+
| USER 1   | 1111        | 111         | 1222         |
| USER 2   | 999         | 222         | 1221         |
| USER 3   | 888         | 333         | 1221         |
| USER 4   | 777         | 444         | 1221         |
+----------+-------------+-------------+--------------+ 

What I am trying to do is, to show the field with the max value.

In this case: User 1 in the season14/15.

Eventually the the Output on the website should be something like:

"The alltime Highscore in a season was USER 1 with 1111 points in season 14/15".

I have tried several things like GREATEST and also subqueries.

No success so far.

Answer 1

You can simply group by user and season, sum the points,and order by aggregated points in descending order, and then take the first result (e.g. using TOP clause in SQL Server)

SELECT TOP 1 p.userid, m.season, sum(points) as points
FROM p INNER JOIN m ON p.userid = m.userid
GROUP BY p.userid, m.season
ORDER BY points desc