How do i get data of a row with MAX(field) for each unique id

Question

I have one table GameData

--id(primary)--playTime--gameId--score--userId(foreign)
   1             100       1      50     asd
   2             150       1      100    asd
   3             200       2      150    asd
   4             200       2      40     qwe
   5             100       1      60     qwe
   6             90        1      30     qwe
   7             180       2      10     qwe

Given userId i want to get rows with highest playTime for each unique gameId.

For example if userId is = "asd" query should return:

[
{playTime:150,gameId:1,score:100},{playTime:200,gameId:2,score:150}


]

if userId is = "qwe" query should return:

   [
    {playTime:100,gameId:1,score:60},{playTime:200,gameId:2,score:40}


    ]

This is what i tried but it doenst give desired result:

 "SELECT GameData.score,MAX(GameData.playTime) as playTime,GameData.gameId FROM GameDataWHERE GameData.userId=? GROUP BY GameData.gameId"

Answer 1

You may try below query -

SELECT GD.playTime, GD.gameId, GD.score
FROM (SELECT MAX(playTime) playTime, gameId
      FROM GameData
      WHERE userId = 'qwe'   -- Or asd
      GROUP BY gameId) M_ID
JOIN GameData GD ON M_ID.playTime = GD.playTime
                 AND M_ID.gameId = GD.gameId