Considerations for using MAX functions and Group By functions in MySQL

mysql> select * from test;+----+-------+------+-------+| id | name  | age  | class |+----+-------+------+-------+|  1 | qiu   |   22 |     1 | |  2 | liu   |   42 |     1 | |  4 | zheng |   20 |     2 | |  3 | qian  |   20 |     2 | |  0 | wang  |   11 |     3 | |  6 | li    |   33 |     3 | +----+-------+------+-------+6 rows in set (0.00 sec)

To find the largest age in each class, use group by and max.

If the following SQL statement is used, the output result is incorrect:

mysql> select id,name,max(age),class from test group by class;+----+-------+----------+-------+| id | name  | max(age) | class |+----+-------+----------+-------+|  1 | qiu   |       42 |     1 | |  4 | zheng |       20 |     2 | |  0 | wang  |       33 |     3 | +----+-------+----------+-------+3 rows in set (0.00 sec)

Although the age found is the largest age, the matched user information is not the actual information, but the basic information of the first record after group.

If I use the following statement for search, the actual results can be returned.

mysql> select * from (    -> select * from test order by age desc) as b    -> group by class;+----+-------+------+-------+| id | name  | age  | class |+----+-------+------+-------+|  2 | liu   |   42 |     1 | |  4 | zheng |   20 |     2 | |  6 | li    |   33 |     3 | +----+-------+------+-------+3 rows in set (0.00 sec)