RANK() OVER (ORDER BY) for multiple criterias-CodePudding

I have two tables, "Players" and "Results":

The goal is to get the following table using a SQL-query (the table ranks the top result per player and orders result from highest to lowest. If there are two equal results, then the player whose name is first alphabetically goes on top).

I tried the query below and I get the following table as a result:

SELECT RANK() OVER(ORDER BY MAX(result) DESC), name, MAX(result)
FROM Results
JOIN Players
WHERE Results.player_id = Players.id
GROUP BY name

I can't figure out how to include the second criteria of ranking the players with the equal score based on their names alphabetical order.

CodePudding user response：

select name , max(result) result, rank() over (order by max(result) desc) rn
from Results r
join Players p on r.player_id = p.id
group by name
order by order by max(result) desc, name