SQL return maximum for groupby-CodePudding

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

CodePudding user response：

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

CodePudding user response：

You’ll need to use the partition approach to find the top within each group. There’s a good explanation here : http://www.silota.com/docs/recipes/sql-top-n-group.html

CodePudding user response：

Same soluntion as that of @eshirvana, but it uses Rank() - If multiple players have same max score,it list all of them.

WITH RANKING(gameid,levelid,playerid,score,rn) AS
 (
   select gameid,levelid,playerid,score, 
          Rank() over (partition by gameid,levelid order by score desc) rn 
     from scores
 )
 select gameid,levelid,playerid,score from ranking where rn=1