CODEWITHSUNDEEP
mysqlsql
Ando
Ando

Reputation: 1827

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

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

enter image description here

SQLFiddle Demo

Upvotes: 2

Views: 90

Answers (3)

John Woo
John Woo

Reputation: 263693

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

Upvotes: 1

Muhammad Raheel
Muhammad Raheel

Reputation: 19882

Try this query

select 
    c.id as country_id,
    c.name as Country,
    c.code as CCode,
    g.T_Games,
    p.id as Player_id,
    p.name as Player
from country as c
left join (select country , count(country) as T_Games from games group by country) as g on g.country = c.id
left join (select id , country , player_id , max(rating) from games group by country) as gl on gl.country = c.id
left join (select id , name from player) as p on p.id = gl.player_id

Here is the demo on sql fiddle

http://sqlfiddle.com/#!2/f74a7/1

Upvotes: 0

Kuzgun
Kuzgun

Reputation: 4737

select top 1 * from games inner join players on games.player_id=players.id inner join country on games.country=country.id order by rating desc

Upvotes: 0

Related Questions