Pablo Fernandez heelhook Pablo Fernandez heelhook - 1 month ago 9
SQL Question

Get row with highest or lowest value from a GROUP BY

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

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()


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
.

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?

Answer Source

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

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