Get highest playerscore from each game - SQL

Question

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

*show them both I guess* , now this will be for you to decide, do you want to show them both ? What MySQL version are you using ? — Ergest Basha, Dec 13 '22 at 11:06
I havent thought about showing ties, but i think showing the ties will be interesting and i'll learn more (perhaps). Im using version 5.7 — WorkInProgress, Dec 13 '22 at 11:18
@WorkInProgress [here](https://dbfiddle.uk/1xpv_LMt) you have a fiddle , but it requires MySQL 8. — Ergest Basha, Dec 13 '22 at 11:20
5.7 is nearing end of life and soon will no longer receive security updates; plan to upgrade as soon as you can. — ysth, Dec 13 '22 at 16:30

blobtub · Answer 1 · 2022-12-13T11:35:09.153

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).

infinite789 · Answer 2 · 2022-12-13T12:17:24.753

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:

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

edited to include player code, but the ties are still present — infinite789, Dec 13 '22 at 12:08

score 0 · Answer 3 · answered Dec 13 '22 at 10:59

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.

answered Dec 13 '22 at 10:59

ahmed ramzan

41
4

This still lists games multiple times, when i only want the top score each game has. – WorkInProgress Dec 13 '22 at 11:05
ORDER BY doesn't help get the highest score for each game; this would only work if only selecting a single game – ysth Dec 13 '22 at 16:32

Get highest playerscore from each game - SQL

3 Answers3