Ivo Ivo - 3 months ago 16
MySQL Question

MySQL userscore per round

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
FROM result
GROUP by user_id, round_id
ORDER by round_id ASC, MAX(result_points) DESC


Query result:

Query result

Seems to be fine, thing is: I only need the user with the highest score per round.

Like this:

preferred score

Do you have any idea how to accomplish this with the given query?

Answer

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.