MySQL: Getting highest score for a user

Question

I have the following table (highscores),

id      gameid      userid      name      score      date
1       38          2345        A         100        2009-07-23 16:45:01
2       39          2345        A         500        2009-07-20 16:45:01
3       31          2345        A         100        2009-07-20 16:45:01
4       38          2345        A         200        2009-10-20 16:45:01
5       38          2345        A         50         2009-07-20 16:45:01
6       32          2345        A         120        2009-07-20 16:45:01
7       32          2345        A         100        2009-07-20 16:45:01

Now in the above structure, a user can play a game multiple times but I want to display the "Games Played" by a specific user. So in games played section I can't display multiple games. So the concept should be like if a user played a game 3 times then the game with highest score should be displayed out of all.

I want result data like:

id      gameid      userid      name      score      date
2       39          2345        A         500        2009-07-20 16:45:01
3       31          2345        A         100        2009-07-20 16:45:01
4       38          2345        A         200        2009-10-20 16:45:01
6       32          2345        A         120        2009-07-20 16:45:01

I tried following query but its not giving me the correct result:

SELECT id, 
       gameid, 
       userid, 
       date, 
       MAX(score) AS score 
  FROM highscores
 WHERE userid='2345' 
GROUP BY gameid 

Please tell me what will be the query for this?

Thanks

Answer 1

Requirement is a bit vague/confusing but would something like this satisfy the need ?
(purposely added various aggregates that may be of interest).

SELECT gameid, 
       MIN(date) AS FirstTime, 
       MAX(date) AS LastTime,
       MAX(score) AS TOPscore.
       COUNT(*)  AS NbOfTimesPlayed 
FROM highscores
WHERE userid='2345' 
GROUP BY gameid
-- ORDER BY COUNT(*) DESC -- for ex. to have games played most at top

Edit: New question about adding the id column to the the SELECT list
The short answer is: "No, id cannot be added, not within this particular construct". (Read further to see why) However, if the intent is to have the id of the game with the highest score, the query can be modified, using a sub-query, to achieve that.

As explained by Alex M on this page, all the column names referenced in the SELECT list and which are not used in the context of an aggregate function (MAX, MIN, AVG, COUNT and the like), MUST be included in the ORDER BY clause. The reason for this rule of the SQL language is simply that in gathering the info for the results list, SQL may encounter multiple values for such an column (listed in SELECT but not GROUP BY) and would then not know how to deal with it; rather than doing anything -possibly useful but possibly silly as well- with these extra rows/values, SQL standard dictates a error message, so that the user can modify the query and express explicitly his/her goals.

In our specific case, we could add the id in the SELECT and also add it in the GROUP BY list, but in doing so the grouping upon which the aggregation takes place would be different: the results list would include as many rows as we have id + gameid combinations the aggregate values for each of this row would be based on only the records from the table where the id and the gameid have the corresponding values (assuming id is the PK in table, we'd get a single row per aggregation, making the MAX() and such quite meaningless).

The way to include the id (and possibly other columns) corresponding to the game with the top score, is with a sub-query. The idea is that the subquery selects the game with TOP score (within a given group by), and the main query's SELECTs any column of this rows, even when the fieds wasn't (couldn't be) in the sub-query's group-by construct. BTW, do give credit on this page to rexem for showing this type of query first.

SELECT H.id, 
       H.gameid, 
       H.userid, 
       H.name,
       H.score,
       H.date        
FROM highscores H
JOIN (
  SELECT M.gameid, hs.userid, MAX(hs.score) MaxScoreByGameUser
  FROM highscores H2
  GROUP BY H2.gameid, H2.userid
) AS M  
   ON M.gameid = H.gameid 
      AND M.userid = H.userid
      AND M.MaxScoreByGameUser = H.score
WHERE H.userid='2345' 

A few important remarks about the query above

• Duplicates: if there the user played several games that reached the same hi-score, the query will produce that many rows.
• GROUP BY of the sub-query may need to change for different uses of the query. If rather than searching for the game's hi-score on a per user basis, we wanted the absolute hi-score, we would need to exclude userid from the GROUP BY (that's why I named the alias of the MAX with a long, explicit name)
• The userid = '2345' may be added in the [now absent] WHERE clause of the sub-query, for efficiency purposes (unless MySQL's optimizer is very smart, currently all hi-scores for all game+user combinations get calculated, whereby we only need these for user '2345'); down side duplication; solution; variables.

There are several ways to deal with the issues mentioned above, but these seem to be out of scope for a [now rather lenghty] explanation about the GROUP BY constructs.

Answer 2

Every field you have in your SELECT (when a GROUP BY clause is present) must be either one of the fields in the GROUP BY clause, or else a group function such as MAX, SUM, AVG, etc. In your code, userid is technically violating that but in a pretty harmless fashion (you could make your code technically SQL standard compliant with a GROUP BY gameid, userid); fields id and date are in more serious violation - there will be many ids and dates within one GROUP BY set, and you're not telling how to make a single value out of that set (MySQL picks a more-or-less random ones, stricter SQL engines might more helpfully give you an error).

I know you want the id and date corresponding to the maximum score for a given grouping, but that's not explicit in your code. You'll need a subselect or a self-join to make it explicit!

Answer 3

Use:

SELECT t.id, 
       t.gameid, 
       t.userid, 
       t.name,
       t.score,
       t.date        
  FROM HIGHSCORES t
  JOIN (SELECT hs.gameid, 
               hs.userid,
               MAX(hs.score) 'max_score'
          FROM HIGHSCORES hs
      GROUP BY hs.gameid, hs.userid) mhs ON mhs.gameid = t.gameid
                                        AND mhs.userid = t.userid
                                        AND mhs.max_score = t.score
 WHERE t.userid = '2345'