[Solved] MySQL Error: ERROR 1055 (42000)sql_mode=only_full_group_by

environment

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.16    |
+-----------+

Execute query statement

mysql> select * from my_student;
+----+--------+----------+------+--------+
| id | name   | class_id | age  | gender |
+----+--------+----------+------+--------+
|  1 | 刘备   |        1 |   18 |      2 |
|  2 | 李四   |        1 |   19 |      1 |
|  3 | 王五   |        2 |   20 |      2 |
|  4 | 张飞   |        2 |   21 |      1 |
|  5 | 关羽   |        1 |   22 |      2 |
|  6 | 曹操   |        1 |   20 |   NULL |
+----+--------+----------+------+--------+
6 rows in set (0.00 sec)

-- Requirement: After sorting by age, take out the oldest students in each class
select * from (
    select * from my_student order by age desc
) as t group by t.class_id;

report errors

ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause 
and contains nonaggregated column 't.id' 
which is not functionally dependent on columns in GROUP BY clause; 
this is incompatible with sql_mode=only_full_group_by

reason:

MySQL 5.7.5 and above function dependency detection function

Solution:

-- View current configuration items
select @@global.sql_mode

-- remove ONLY_FULL_GROUP_BY
set @@global.sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

After setting, if it does not take effect, you can exit and log in again

Read More: