SQL Group By: Get values of 'max' record

Question

I have a scores table:

id
user
score
date

Now, I can easily select a top 10 highscore with

SELECT user, score, date FROM scores ORDER BY score DESC

However, I'd like to include only one score per user, namely his highest. I would begin with something like

SELECT user, MAX(score) AS score FROM scores GROUP BY user ORDER BY score DESC

However, now I've lost the date that highest score was recorded. How do I get it?

Taryn · Answer 1 · 2012-08-20T22:49:38.017

1

You can JOIN on the table again:

SELECT s1.user, max(s1.dt), s2.mxscore as score
FROM scores s1
inner join 
(
    select user, max(score) mxscore
    from scores
    GROUP BY user 
) s2
    on s1.user = s2.user
    and s1.score = s2.mxscore
GROUP BY s1.username, s2.mxscore
ORDER BY score DESC

See SQL Fiddle with Demo

edited Aug 20 '12 at 22:49

answered Aug 20 '12 at 22:15

Taryn

242,637
56
362
405

What happens if there's more than one date with the same score? – Barranka Aug 20 '12 at 22:17
It's unlikely that the `id` field is the user ID, but rather an auto-incremented field which acts as a primary key for each scoring instance. – Zane Bien Aug 20 '12 at 22:35

score 1 · Answer 2 · answered Aug 21 '12 at 00:47

In fact, you don't need a GROUP BY at all.

Here's the query:

SELECT scores.id, scores.user, scores.score, scores.date
FROM scores
WHERE NOT EXISTS (
  SELECT * 
  FROM scores AS _scores
  WHERE _scores.user = scores.user
  AND (
    _scores.score > scores.score
    OR
    _scores.score = scores.score AND _scores.id < scores.id) 
)

and SQL Fiddle to see it working.

Note that this query properly handles the case when a user had his max score several times (it returns the record for the first max score).

score 0 · Answer 3 · answered Aug 20 '12 at 22:16

You will need to relate your result with your original table:

select a.user, a.maxScore, b.maxDate
from (
    select user, max(score) as maxScore 
    from scores group by user ) as a
inner join (
    select user, score, max(date) as maxDate 
    from scores group by user, score) as b on a.user = b.user and a.maxScore=b.score
order by
    a.maxScore desc

This query will return the maximum score for each user, and the last date when this maximum score was scored (redundant, but true)

score 0 · Answer 4 · answered Aug 20 '12 at 22:21

SELECT   a.*
FROM     scores a
JOIN     (
         SELECT   MAX(a.id) AS id
         FROM     scores a
         JOIN     (
                  SELECT   user, MAX(score) AS score
                  FROM     scores
                  GROUP BY user
                  ) b ON a.user  = b.user
                     AND a.score = b.score
         GROUP BY a.user, 
                  a.score
         ) b ON a.id = b.id
ORDER BY a.score DESC

This will account for cases where you have more than one of the same highest score per user. In that case, it will just take the maximum id.

SQL Group By: Get values of 'max' record

4 Answers4