Q:

SQL for getting top user based on rating and using result to get additional data

I'm trying to come up with an SQL which gets every

country's  name,
code,
number of games,
TOP player's name and
his id
based on the SUM(rating) in the games table.

I'm having trouble with getting the name of the top player based on the rating.

SQLFiddle Demo

A:

SELECT  x.Country AS CountryName,
        x.Code,
        a.totalCount as NumberOfGames,
        y.Name AS PlayersName,
        y.ID AS PlayersID,
        a.totalRating
FROM    (
            SELECT  player_ID, Country, COUNT(*) totalCount, SUM(Rating) totalRating
            FROM    Games
            GROUP BY player_ID, Country
        ) a 
        INNER JOIN
        (
            SELECT Country, Max(totaLRating) maxRating
            FROM
            (
                SELECT  player_ID, Country, SUM(Rating) totalRating
                FROM    Games
                GROUP BY player_ID, Country
            ) s
            GROUP BY Country
        ) b ON a.Country = b.Country AND
                a.totalRating = b.maxRating
        INNER JOIN Country x
            ON a.Country = x.ID
        INNER JOIN Players y
            ON a.player_ID = y.ID

SQLFiddle Demo

