SQL return maximum for groupby

November 6, 2021

I have the following database with 4 tables: scores, players, games, levels.

CREATE TABLE games(
   gameid     INTEGER  NOT NULL 
  ,name   VARCHAR(6) NOT NULL
  ,active VARCHAR(8) NOT NULL,
  PRIMARY KEY (gameid)
);

INSERT INTO games(gameid,name,active) VALUES (1,'pang','yes');
INSERT INTO games(gameid,name,active) VALUES (2,'pong','yes');
INSERT INTO games(gameid,name,active) VALUES (3,'pung','yes');

CREATE TABLE levels(
   levelid     INTEGER  NOT NULL
  ,name   VARCHAR(6) NOT NULL
  ,active VARCHAR(8) NOT NULL,
    PRIMARY KEY (levelid)

);
INSERT INTO levels(levelid,name,active) VALUES (1,'pang','yes');
INSERT INTO levels(levelid,name,active) VALUES (2,'pong','yes');
INSERT INTO levels(levelid,name,active) VALUES (3,'pung','yes');

CREATE TABLE player(
   playerid     INTEGER  NOT NULL 
  ,name   VARCHAR(6) NOT NULL
  ,surname VARCHAR(8) NOT NULL,
    PRIMARY KEY (playerid)

);
INSERT INTO player(playerid,name,surname) VALUES (1,'pang','yes');
INSERT INTO player(playerid,name,surname) VALUES (2,'pong','yes');
INSERT INTO player(playerid,name,surname) VALUES (3,'pung','yes');



CREATE TABLE scores (
    gameid int NOT NULL,
    levelid int NOT NULL,
    playerid int NOT NULL,
    score int NOT NULL

);

INSERT INTO scores(gameID,levelID, playerID, score) VALUES (1,1,1,50);
INSERT INTO scores(gameID,levelID, playerID, score) VALUES (1,1,2,60);
INSERT INTO scores(gameID,levelID, playerID, score) VALUES (1,1,3,100);
INSERT INTO scores(gameID,levelID, playerID, score) VALUES (2,1,2,60);
INSERT INTO scores(gameID,levelID, playerID, score) VALUES (2,1,3,500);

I’m trying to return the player with the topic score for each level of each game. At the moment, I can’t seem to figure out why I’m reassigning the max score to each player. My code is the following:

SELECT g.gameid, l.levelid, p.playerid, ts.top_score
FROM scores s
INNER JOIN games g ON g.gameid = s.gameid
INNER JOIN levels l ON l.levelid = s.levelid
INNER JOIN player p ON p.playerid = s.playerid

INNER JOIN (
  SELECT gameID, levelid, MAX(score) AS top_score FROM scores GROUP BY gameid, levelid
  ) ts ON (s.gameid = ts.gameid AND s.levelid = ts.levelid)

GROUP BY g.gameid, l.levelid, p.playerid, ts.top_score

DB fiddle available: https://www.db-fiddle.com/f/peyKD96aPmZTjY1MSn1ik2/4

Desired output is:

gameid, levelid, playerid, score 
  1,       1,      3,       100
  2,       1,      3,       500

>Solution :

If I understand correctly , you want this :

select g.gameid, l.levelid, p.playerid, s.score 
from (
  select * , row_number() over (partition by gameid,levelid order by score desc) rn 
  from scores
) s 
join games g on s.gameId = g.gameid
join levels l on l.levelid = s.levelid
join player p on p.playerid = s.playerid
where s.rn = 1;

however If you are not selecting any columns from l,p or g, you really don’t need to join with those tables at all