Get row with highest or lowest value from a GROUP BY

Question

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 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.