Get max score and the fields in the max score row in MySQL 5.7

Question

| id | uid  | score   | date    |
————————————————————————————————
| 1  | aaa  | 10      | 2017.7.7|

| 2  | bbb  | 5       | 2017.7.7|

| 3  | aaa  | 15      | 2017.7.7|

| 4  | bbb  | 20      | 2017.7.8|

I want to get everyone’s max score and every row should include all fields like id and date.

| id | uid | score | date    |
—————————————————————————————
| 4  | bbb | 20    | 2017.7.8|

| 3  | aaa | 15    | 2017.7.7|

Select max(score) as score, id, date, uid from data group by uid order by score desc

I can use this sql to get what I want in MySQL before v5.7 but you know after MySQL 5.7 there is a sql_mode ONLY_FULL_GROUP_BY. So I can’t get the result as before. I see there are some answers tell me to disable the mode. But I want to know if there are any sql can do this as well as I don’t disable the mode.

Answer 1

Another approach to rewrite the same query by using left join without use of aggregate function

select a.*
from data a
left join data b on a.uid = b.uid
and a.score < b.score
where b.uid is null

Note it may return multiple rows for single uid if there are rows with same highest score you might need a CASE statement and another attribute to decide which row should be picked to handle this kind of situation

Demo using MySQL 5.7.12

Answer 2

i suggest you to change this property for only this query by using

SET sql_mode = 'ONLY_FULL_GROUP_BY';

Answer 3

You can disable the mode with following query:

mysql > SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

Also, your query will only show max score of uid but not it's relevant id and date.

Select max(score) as score, id, date, uid 
from data 
where (uid,score) in (select uid,max(score) 
                      from data
                      group by uid
                     )
group by uid 
order by score desc