获取组中值最高或最低的行

I'm trying to get the row with the highest/lowest number, after performing a GROUP BY:

我试着在执行了一个GROUP BY之后，得到最高/最低的行:

Here is my test data

这是我的测试数据

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)

To get the lowest value, I'll use MIN()

为了得到最小值，我将使用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)

Now, the id row2 is 2, but it should be 4.

id row2是2，但应该是4。

I also tried with a join:

我也尝试了加入:

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)

How can I get the correct ID for each result based on what the lowest value is?

根据最低的值，如何得到每个结果的正确ID ?

1 个解决方案

#1

I think this is what you are trying to achieve:

我认为这就是你想要达到的目标:

SELECT t.* FROM test t
JOIN 
( SELECT Name, MIN(Value) minVal
  FROM test GROUP BY Name
) t2
ON t.Value = t2.minVal AND t.Name = t2.Name;

Output:

输出:

╔════╦═══════╦══════╗
║ ID ║ VALUE ║ NAME ║
╠════╬═══════╬══════╣
║  1 ║    10 ║ row1 ║
║  4 ║     5 ║ row2 ║
╚════╩═══════╩══════╝

See this SQLFiddle

• Demo with more values
• 演示更值
• Demo with duplicate values
• 演示与重复的值
• Demo with removing duplicate values (using DISTINCT)
• 演示，删除重复值(使用不同的值)

Here I have self-joined the table with minVal and Name.

在这里，我用minVal和Name自组了桌子。