Question 21778 in Mysql Question posted in
The official MySQL documentation can be found here.

Get highest playerscore from each game – SQL – Mysql

I need to find the highest score for each game. along with the gamename, category, PlayerCode (name of player) who got the highscore, date (time) when the score was set and ofc the score itself.

My tables are set up as follows

CREATE TABLE Player(
PlayerId INT,
Date DATE,
PlayerCode CHAR(3),
PRIMARY KEY (PlayerId)
);

CREATE TABLE Game(
GameId INT,
Name VARCHAR(128),
Description VARCHAR(256),
Launched DATE,
Category ENUM('Adventure', 'Action', 'RPG', 'Simulation', 'Sports', 'Puzzle', 'Other'),
PRIMARY KEY (GameId)
);

CREATE TABLE Results(
PlayerId INT,
GameId INT,
Time DATETIME,
Score INT,
PRIMARY KEY (PlayerId,GameId,Time),
CONSTRAINT ResultsGameFK FOREIGN KEY (GameId) REFERENCES Game (GameId),
CONSTRAINT ResultsPlayerFK FOREIGN KEY (PlayerId) REFERENCES Player (PlayerId)
);

So far i can i can only get each score for each player in each game. When the top score in each game is what im looking after. I only recently learned about joins, but this one has me confused. Thank you

Select Game.Name, Game.Category, PlayerCode, Score, Time
from  Player
JOIN Results
ON Player.PlayerId = Results.PlayerId
JOIN Game
ON Game.GameId = Results.GameId 
group by Game.Name, Game.Category, Score, PlayerCode, Time
order by Score DESC
Tags:

3

Answers


  1. 0

    You have almost done that. you have already applied GROUP BY clause, it means you can perform aggregation.

    In your select clause do this

    Select Game.Name, Game.Category, PlayerCode, MAX(Score), Time

    By MAX(Score), you can get greatest score, and you have already ORDER BY score DESC, that means you will get highr score first.

    Login or Signup to reply.
  2. 0

    Ideally you would use RANK, but if that’s not available (for example you have an older database version), a solution that allows multiple players to tie a highscore can be written:

    SELECT 
  Game.Name as GameName, 
  Game.Category AS GameCategory,
  Player.PlayerCode AS PlayerCode, 
  Results.R_Time AS HighscoreTime,
  Results.Score AS Highscore
FROM Results
JOIN (
  SELECT 
    GameId, 
    MAX(Score) AS max_score
  FROM Results
  GROUP BY GameId ) ms
ON Results.GameId = ms.GameId
AND Results.Score = ms.max_score
JOIN Game
ON Results.GameId = Game.GameId
JOIN Player
ON Results.PlayerId = Player.PlayerId;

    If you can use RANK, and having more than one highscore is acceptable (i.e. for ties), try:

    SELECT 
  Game.Name as GameName, 
  Game.Category AS GameCategory,
  Player.PlayerCode AS PlayerCode, 
  rr.R_Time AS HighscoreTime,
  rr.Score AS Highscore
FROM (
  SELECT 
    PlayerId, 
    GameId, 
    R_Time, 
    Score,
    RANK() OVER(PARTITION BY GameId ORDER BY Score DESC) AS rank_score
  FROM Results ) rr
JOIN Game
ON rr.GameId = Game.GameId
JOIN Player
ON rr.PlayerId = Player.PlayerId
WHERE rr.rank_score = 1;

    If you must have at most one result per game, change the RANK to:

    RANK() OVER(PARTITION BY GameId ORDER BY Score DESC, R_Time, PlayerID) AS rank_score

    The logic there is the player with the highest, earliest, score gets the high score. (…breaking the tie by the player ID if those are the same — this may not be what you want — adapt as required)

    Note I changed a couple of your column names to avoid clashes with SQL keywords (Time, Date).

    Login or Signup to reply.
  3. 0

    There you go:

    select * 
from game
right join (select c.GameId, Max(c.Score)
            from player 
            right join (select results.PlayerId, results.GameId, results.Time, results.Score, game.Name, game.Description, game.Launched, game.Category
                        from results 
                        left join game 
                        on results.GameId = game.GameId) AS c
            on player.PlayerId = c.PlayerId
            group by c.GameId
            order by Max(c.Score)) as d
on game.GameId = d.GameId

    Will give you the highest score for each game in your dataset:

    enter image description here

    EDIT:

    if you don’t mind the ties and the many subqueries then this includes the player:

    select * 
from player
right join (select game.GameId, game.Name, game.Description, game.Launched, game.Category, e.playerId, e.Time, e.maxScore
            from game
            right join (select results.playerId, results.GameId, results.Time, d.maxScore
                        from results
                        right join (select c.GameId, Max(c.Score) as maxScore
                                    from player 
                                    right join (select results.PlayerId, results.GameId, results.Time, results.Score, game.Name, game.Description, game.Launched, game.Category
                                                from results 
                                                left join game 
                                                on results.GameId = game.GameId) AS c
                                    on player.PlayerId = c.PlayerId
                                    group by c.GameId
                                    order by Max(c.Score)) AS d
                        on results.GameId = d.GameId
                        where results.score = d.maxScore) AS e
            on game.GameId = e.GameId) AS f
on player.playerId = f.playerId

    enter image description here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search