Currently I'm struggling with a MySQL query. I want to calculate the score for a user per round. I'm trying to get the user that has the best score for a certain round.
My current result:
SELECT user_id, SUM(result_points), round_id
GROUP by user_id, round_id
ORDER by round_id ASC, MAX(result_points) DESC
You'll want to use the query that you have as a subquery along with the MAX function on the total points per round that you are getting from your query.
SELECT user_id, MAX(roundResults.points) as points, round_id FROM (SELECT user_id, SUM(result_points) as points, round_id FROM result GROUP by user_id, round_id ORDER by round_id, points DESC) AS roundResults GROUP by round_id
See the fiddle here.
Note: ORDER BY points DESC preserves the user_id placement.