I have a table like this:
| Key | Value | Message |
|-----|-------|---------|
| a | 1 | xx |
| a | 2 | yy |
| b | 5 | mm |
| b | 4 | nn |
I would like to group the data by Key, get the minimum Value of each group, and the related Message. The result is expected to be:
| Key | Value | Message |
|-----|-------|---------|
| a | 1 | xx |
| b | 4 | nn |
I'm using MySQL 5.7. Is it possible to do that?
You can try below -
select * from tablename a
where value = (select min(value) from tablename b where a.key=b.key)
You can use row_number() :
select t.*
from (select t.*, row_number() over (partition by key order by value) as seq
from table t
) t
where seq = 1;
You can also use correlated subquery if ranking function not support :
select t.*
from table t
where t.value = (select min(t1.value) from table t1 where t1.key = t.key);
The canonical way to handle this in MySQL 5.7 or earlier would be to join to a subquery which finds the smallest value for each key:
SELECT t1.`Key`, t1.`Value`, t1.Message
FROM yourTable t1
INNER JOIN
(
SELECT `Key`, MIN(`Value`) AS min_value
FROM yourTable
GROUP BY `Key`
) t2
ON t1.`Key` = t2.`Key` AND t1.`Value` = t2.min_value
ORDER BY
t1.`Key`;
Please try to avoid naming your columns and other database objects using reserved SQL keywords, such as Key and Value. You may consider the need to type backticks in a MySQL query as an anti-pattern.
First create a subquery that returns min value for each key, then join with the main table:
select t.*
from tablename t
inner join (
select key, min(value) min_value
from tablename
group by key
) i on i.key = t.key and i.min_value = t.value
Use this Query
select * from table_name tab1
where value in ( select MIN(value) from table_name tab2
where tab2.[key] = tab1.[key] )
