在写业务代码时，遇到GROUP BY取数据问题，记录如下。
MySQL 版本5.6
假设我们的表结构如下：
表：message
| mes_id | order_code | user_code | remark |
|---|---|---|---|
| 1 | O123 | C1001 | 测试 |
| 2 | O223 | C1002 | 备注 |
| 3 | O123 | C9527 | 测试22 |
现需要按order_code分组，取最大mes_id一条。
最开始的思路是先将message按mes_id倒序，然后再GROUP BY。
SELECT * FROM ( SELECT * FROM message ORDER BY mes_id DESC ) m1 GROUP BY order_code;
结果并没有正确输出。没有使用到子查询的排序。
为啥呢？
原来MySQL5.7版本之前不参与grouping字段是不确定返回哪条数据的。
文档：
The select list extension also applies to ORDER BY. That is, you can refer to nonaggregated columns in the ORDER BY clause that do not appear in the GROUP BY clause. (However, as mentioned previously, ORDER BY does not affect which values are chosen from nonaggregated columns; it only sorts them after they have been chosen.) This extension does not apply if the ONLY_FULL_GROUP_BY SQL mode is enabled.
所以刚刚SQL可以改为：
SELECT * FROM ( SELECT MAX( mes_id ) mes_id FROM message GROUP BY order_code ) m1 LEFT JOIN message m ON m1.mes_id = m.mes_id ORDER BY m.mes_id DESC;
MySQL5.7之后版本，仍然是未定义状态，并且默认直接不允许未定义状态的grouping查询，会报错：
mysql> SELECT name, MAX(age) FROM t; ERROR 1140 (42000): In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'mydb.t.name'; this is incompatible with sql_mode=only_full_group_by
具体看文档 ONLY_FULL_GROUP_BY
参考：