mysql5.x any_value()查询非聚合函数字段以及分组排序

• mysql 版本

mysql> select version() from dual;
+------------+
| version()  |
+------------+
| 5.7.23-log |

• 表结构

mysql> desc data_index;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | int(10)      | NO   | PRI | NULL    | auto_increment |
| sort        | int(10)      | YES  |     | NULL    |                |
| group_key   | varchar(255) | YES  |     | NULL    |                |
| group_name  | varchar(255) | YES  |     | NULL    |                |
| index_key   | varchar(255) | YES  |     | NULL    |                |
| index_name  | varchar(255) | YES  |     | NULL    |                |
| index_value | bigint(10)   | YES  |     | NULL    |                |
| index_time  | datetime     | YES  |     | NULL    |                |
| create_time | datetime     | YES  |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+
9 rows in set (0.03 sec)

• 表数据

mysql> select * from data_index;
+-----+------+------------+------------+-----------+------------+-------------+---------------------+---------------------+
| id  | sort | group_key  | group_name | index_key | index_name | index_value | index_time          | create_time         |
+-----+------+------------+------------+-----------+------------+-------------+---------------------+---------------------+
| 161 |    1 | data_index | 指标统计   | one       | 一         |         230 | 2022-03-09 15:10:33 | 2022-03-09 15:10:33 |
| 162 |    2 | data_index | 指标统计   | tow       | 二         |         210 | 2022-02-09 00:00:00 | 2022-03-09 15:10:33 |
| 163 |    3 | data_index | 指标统计   | three     | 三         |         190 | 2022-01-09 00:00:00 | 2022-03-09 15:10:33 |
| 164 |    4 | data_index | 指标统计   | four      | 四         |         170 | 2021-12-09 00:00:00 | 2022-03-09 15:10:33 |
| 165 |    5 | data_index | 指标统计   | five      | 五         |         150 | 2021-11-09 00:00:00 | 2022-03-09 15:10:33 |
+-----+------+------------+------------+-----------+------------+-------------+---------------------+---------------------+

• 根据 index_key 分组并按照 sort 降序排列

mysql> SELECT
	any_value ( sort ),
	any_value ( group_key ),
	any_value ( group_name ),
	index_key,
	any_value ( index_name ) 
FROM
	data_index 
GROUP BY 
	index_key 
ORDER BY 
	any_value ( sort ) DESC;
+--------------------+-------------------------+--------------------------+-----------+--------------------------+
| any_value ( sort ) | any_value ( group_key ) | any_value ( group_name ) | index_key | any_value ( index_name ) |
+--------------------+-------------------------+--------------------------+-----------+--------------------------+
|                  5 | data_index              | 指标统计                 | five      | 五                       |
|                  4 | data_index              | 指标统计                 | four      | 四                       |
|                  3 | data_index              | 指标统计                 | three     | 三                       |
|                  2 | data_index              | 指标统计                 | tow       | 二                       |
|                  1 | data_index              | 指标统计                 | one       | 一                       |
+--------------------+-------------------------+--------------------------+-----------+--------------------------+

ps：关于any_value()函数的详细作用可自行查阅
ps：博主并不清楚这样使用是否正确，有懂哥知道的话烦请指正