Ismail Akrim Ismail Akrim - 1 month ago 6
MySQL Question

Can't get the right column value for a query containing max()

I have a set of data like :

Nm | item | type | value
21 | 19 | A | 15
22 | 40 | B | 10
21 | 20 | A | 80
32 | 40 | C | 40


I tried several queries and i always get : (for the record Nm = 21)

Nm | item | type | max(value)
21 | 19 | A | 80


which is not what i want ,since the max value is from the item = 20

Answer

Query:

select *
from item_table it_o
where it_o.value in 
(select max(value)
 from item_table it_i
 where it_i.nm=it_o.nm)

Output:

nm  item    type    value
22  40      B   10
21  20      A   80
32  40     C    40