CODEWITHSUNDEEP
mysqlsqldatabaseoracle-databasegreatest-n-per-group
tsztokma
tsztokma

Reputation: 21

Select min value per item in MySQL

Hello I have the following MySQL table:

id       item         value
1         A             11
2         A             20
3         B              2
4         C              1
5         B             14
6         C             12

What I'm trying to do is to select items with the lowest values - what I tried to do based on search of this forum is to use the following query:

  SELECT *,
         MIN(value) 
    FROM mytable 
GROUP BY item

The expected result should be A => 11, B => 2; C => 1 however for some of the items the min value is correct and for some other items the min value is different value. Please note that mytable consist of approx ~100 rows.

Upvotes: 1

Views: 5420

Answers (4)

Somnath Kadam
Somnath Kadam

Reputation: 6357

If you need all information of min(value) for each item and datatype of "value" column is int, then run following query,

select * from mytable
where (item,value) in
(
   SELECT item, min(value)
   FROM mytable
   GROUP BY item
)

Please see SQL fiddle

Upvotes: 0

Aman Kaur
Aman Kaur

Reputation: 321

You may try this way.

SELECT t.* FROM mytable  t
JOIN 
( SELECT item, MIN(value) minVal
  FROM mytable  GROUP BY item
) t2
ON t.value = t2.minVal AND t.item = t2.item;

Upvotes: 3

Pramod
Pramod

Reputation: 96

select *
from mytable t1
inner join
(
  select min(value) value, id
  from mytable 
  group by id
) t2
  on t1.id = t2.id
  and t1.value= t2.value

Upvotes: 0

davegreen100
davegreen100

Reputation: 2115

this would work in oracle

SELECT item, min(value)
FROM mytable
GROUP BY item

Upvotes: 1

Related Questions