Select distinct multiple columns with MAX value from father table-CodePudding

Table GAMES

gameId	name	score
1	Tom	452
2	Tom	453

Table GAME_DETAILS

gameDetailIds	gameId	posX	posY	time
1	1	-1	-1	1665264019
2	1	-1	0	1665264020
3	1	-1	1	1665264021
4	1	0	-1	1665264022
5	1	0	0	1665264023
6	2	-1	-1	1665264024
7	2	-1	0	1665264025
8	2	-1	1	1665264026

I want to find all of Tom's posX and posY where posX and posY are unique and it belongs to the game with the highest score

gameDetailIds	gameId	posX	posY	time
4	1	0	-1	1665264022
5	1	0	0	1665264023
6	2	-1	-1	1665264024
7	2	-1	0	1665264025
8	2	-1	1	1665264026

Thank you very much!

CodePudding user response：

As I understand it, you want to show all distinct postions, but per position you want to show the "best" row. "Best" means the game with the highest score for that position.

You can use ROW_NUMBER to mark these best rows with #1. Then keep only those rows.

select gamedetailids, gameid, posx, posy, time
from
(
  select
    gameid, gd.gamedetailids, gd.posx, gd.posy, gd.time,
    row_number() over (partition by gd.posx, gd.posy order by g.score desc, gd.time) as rn
  from game_details gd
  join game g using (gameid)
  where g.name = 'Tom'
) ranked
order by posx, posy;