Selecting highest value in column per group

Question

I'm making a DB for a moba game and I'm trying to make a query that will return the hero in each game that has the highest win rate (assume no ties). Each Game has two teams, and each team has five selected_hero which corresponds to a hero. What I want is the Game_Id, Hero.Hero_Name, and Hero.Win_Rate. So far, I'm able to get the Game_Id and Highest win rate in that game, but whenever I try to add in the Hero.Hero_Name, it returns every hero in each game along with their win rate.

Here's the set up for the tables:

CREATE TABLE Game(
    Game_Id INT NOT NULL PRIMARY KEY
);

CREATE TABLE Team (
    Team_Id INT NOT NULL PRIMARY KEY
    Game_Id INT NOT NULL, 
    FOREIGN KEY(Game_Id) REFERENCES Game(Game_Id)
);

CREATE TABLE Hero (
    Hero_Id INT NOT NULL PRIMARY KEY,
    Hero_Name VARCHAR(30),
    Win_Rate DECIMAL(5,4)
);

CREATE TABLE Selected_Hero (
    Hero_Id INT NOT NULL,
    Team_Id INT NOT NULL
    FOREIGN KEY(Hero_Id) REFERENCES Hero(Hero_Id),
    FOREIGN KEY(Team_Id) REFERENCES Team(Team_Id)
);

Here's the query that gives me the game_id and max win_rate per game but no hero name:

SELECT Game.Game_Id, MAX(Hero.Win_Rate)
FROM Game
JOIN Team ON Game.Game_Id = Team.Game_Id 
JOIN Selected_Hero ON Team.Team_Id = Selected_Hero.Team_Id
JOIN Hero ON Selected_Hero.Hero_Id = Hero.Hero_Id
GROUP BY Game.Game_Id;

If I use the following, every hero and their win rate in each game is returned, but it's ordered by game_id and win_rate, so if I could just take the first row from each game_id group, it would give me what I want:

SELECT * FROM (
SELECT DISTINCT Game.Game_Id, Hero.Hero_Name AS heroName, MAX(Hero.Win_Rate) AS winRate
FROM Game
JOIN Team ON Game.Game_Id= Team.Game_Id
JOIN Selected_Hero ON Team.Team_Id = Selected_Hero.Team_Id
JOIN Hero ON Selected_Hero.Hero_Id = Hero.Hero_Id
GROUP BY Game.Game_Id, Hero.Hero_Name
ORDER BY Game_Id, winRate DESC
);

Answer 1

Use RANK:

WITH cte AS (
    SELECT Game.Game_Id,
           RANK() OVER (PARTITION BY Game.Game_Id
                        ORDER BY Hero.Win_Rate DESC) rnk
    FROM Game
    INNER JOIN Team ON Game.Game_Id = Team.Game_Id 
    INNER JOIN Selected_Hero ON Team.Team_Id = Selected_Hero.Team_Id
    INNER JOIN Hero ON Selected_Hero.Hero_Id = Hero.Hero_Id
)

SELECT *
FROM cte
WHERE rnk <= 2;  -- for 1st and 2nd; for 2nd only use rnk = 2