SELECT specific rows when using GROUP BY

Question

I have the following SQL table that keeps track of a user's score at a particular timepoint. A user can have multiple scores per day.

+-------+------------+-------+-----+
| user  | date       | score | ... |
+-------+------------+-------+-----+
| bob   | 2014-04-19 | 100   | ... |
| mary  | 2014-04-19 | 100   | ... |
| alice | 2014-04-20 | 100   | ... |
| bob   | 2014-04-20 | 110   | ... |
| bob   | 2014-04-20 | 125   | ... |
| mary  | 2014-04-20 | 105   | ... |
| bob   | 2014-04-21 | 115   | ... |
+-------+------------+-------+-----+

Given a particular user (let's say bob), How would I generate a report of each user's score, but only use the highest submitted score per day? (Getting the specific row with the highest score is important as well, not just the highest score)

SELECT * FROM `user_score` WHERE `user` = 'bob' GROUP BY `date`

is the base query that I'm building off of. It results in the following result set:

+-------+------------+-------+-----+
| user  | date       | score | ... |
+-------+------------+-------+-----+
| bob   | 2014-04-19 | 100   | ... |
| bob   | 2014-04-20 | 110   | ... |
| bob   | 2014-04-21 | 115   | ... |
+-------+------------+-------+-----+

bob's higher score of 125 from 2014-04-20 is missing. I tried rectifying that with MAX(score)

SELECT *, MAX(score)  FROM `user_score` WHERE `user` = 'bob' GROUP BY `date`

returns the highest score for the day, but not the row that has the highest score. Other column values on that row are important,

+-------+------------+-------+-----+------------+
| user  | date       | score | ... | max(score) |
+-------+------------+-------+-----+------------+
| bob   | 2014-04-19 | 100   | ... | 100        |
| bob   | 2014-04-20 | 110   | ... | 125        |
| bob   | 2014-04-21 | 115   | ... | 110        |
+-------+------------+-------+-----+------------+

Lastly, I tried

SELECT *, MAX(score)  FROM `user_score` WHERE `user` = 'bob' AND score = MAX(score) GROUP BY `date`

But that results in an invalid use of GROUP BY.

• Selecting a row with specific value from a group? is on the right track with what I am trying to accomplish, but I dont know the specific score to filter by.

EDIT:

SQLFiddle: http://sqlfiddle.com/#!2/ee6a2

Answer 1

If you want all the fields, the easiest (and fastest) way in MySQL is to use not exists:

SELECT *
FROM `user_score` us
WHERE `user` = 'bob' AND
      NOT EXISTS (SELECT 1
                  FROM user_score us2
                  WHERE us2.`user` = us.`user` AND
                        us2.date = us.date AND
                        us2.score > us.score
                 );

This may seem like a strange approach. And, I'll admit that it is. What it is doing is pretty simple: "Get me all rows for Bob from user_score where there is no higher score (for Bob)". That is equivalent to getting the row with the maximum score. With an index on user_score(name, score), this is probably the most efficient way to do what you want.

Answer 2

You can use a JOIN:

SELECT a.*
FROM `user_score` as a
INNER JOIN (SELECT `user`, `date`, MAX(score) MaxScore
            FROM `user_score` 
            GROUP BY `user`, `date`) as b
    ON a.`user` = b.`user`
    AND a.`date` = b.`date`
    AND a.score = b.MaxScore
WHERE a.`user` = 'bob'