mysql group by取条数最多_mysql – 从GROUP BY获取具有最高或最低值的行

在执行GROUP BY之后,我试图获得具有最高/最低数字的行：

这是我的测试数据

mysql> SELECT * FROM test;

+----+-------+------+

| id | value | name |

+----+-------+------+

| 1 | 10 | row1 |

| 2 | 12 | row2 |

| 3 | 10 | row2 |

| 4 | 5 | row2 |

+----+-------+------+

4 rows in set (0.00 sec)

要获得最低的值,我将使用MIN()

mysql> SELECT id, name, MIN(value) AS value FROM test GROUP BY name;

+----+------+-------+

| id | name | value |

+----+------+-------+

| 1 | row1 | 10 |

| 2 | row2 | 5 |

+----+------+-------+

2 rows in set (0.00 sec)

现在,id row2是2,但应该是4.

我也试过加入：

mysql> SELECT t1.* FROM

(SELECT id, name, MIN(value) AS value

FROM test GROUP BY name) AS t1

INNER JOIN test AS t2 ON t1.id = t2.id;

+----+------+-------+

| id | name | value |

+----+------+-------+

| 1 | row1 | 10 |

| 2 | row2 | 5 |

+----+------+-------+

2 rows in set (0.00 sec)

如何根据最低价值获得每个结果的正确ID？