Query to get identifier and aggregate function value of a row

Question

Having the following two tables in a MySQL Database:

USER
 - ID
 - Name

GAME1
 - ID
 - UserID
 - Score
 - Rank

This is what I need in a view:

 - User.ID
 - User.Name
 - Game1.ID as MaxScoreGameID
 - Max(Game1.Score) as MaxScore
 - Game1.ID as BestRankGameID
 - Min(Game1.Rank) as BestRank

I got it working without GameIDs, but I also need the IDs of the games where MaxScore and BestRank occurred.

I searched the web for some information and tried GROUP_CONCAT, HAVING, ... but I could not get the results I need.

edit

As a result of the query I except one row per user with the MaxScore and BestRank and the IDs of the games where these were reached. If a User has the same score (which is best) in more than 1 game, I only want one of these in the user row.

Answer 1

SELECT u.ID
     , u.Name
     , (SELECT sub.ID from Game1 as sub where sub.UserID = u.ID ORDER BY sub.Score desc LIMIT 1) as MaxScoreGameID
     , Max(Game1.Score) as MaxScore
     , (SELECT sub2.ID from Game1 as sub2 where sub2.UserID = u.ID ORDER BY Rank asc LIMIT 1) as BestRankGameID
     , Min(Game1.Rank) as BestRank
FROM [User] u
JOIN Game1 on Game1.UserID = u.ID GROUP BY u.ID, u.Name

The idea is to use Subselects to find the max score and use that score to get the Game1.ID. Same idea is used for Game1.Rank.