get id of min value, grouped by another column

Question

I have a table like this

id, price, date
1, 200, 20180923
2, 100, 20180923
3, 300, 20180923
4, 300, 20180924
5, 200, 20180924
6, 100, 20180924

I want to find the minimum price for a given date, and retrieve the ID along with it.

So SELECT id, MIN(price), date FROM table GROUP BY date will return the lowest price for each date, but it doesn't tell me which id it belongs to.

The expected output here would be

2, 100, 20180923
6, 100, 20180924

If multiple ids have the same minimum on a given date, what do you want returned. — Gordon Linoff, Sep 23 '18 at 18:55

score 8 · Accepted Answer · answered Sep 23 '18 at 18:54

Think of this as filtering, not aggregation. I would do:

select t.*
from t
where t.price = (select min(t2.price)
                 from t t2
                 where t2.date = t.date
                );

This has the advantage that it can make use of an index on (date, price).

If there are duplicate minimum prices on a given date, this will retrieve multiple rows.

One way to handle duplicates is to return them as a list:

select t.date, min(t.price), group_concat(t.id) as ids
from t
where t.price = (select min(t2.price)
                 from t t2
                 where t2.date = t.date
                )
group by date;

score 0 · Answer 2 · answered Sep 23 '18 at 18:57

Find minimum price per date Grouping. Use this as a derived table and join with the original table, to get the full row corresponding to minimum price.

Try the following:

Select t1.* 
From your_table as t1 
Inner join (select t2.date, min(t2.price) as min_price 
                   From your_table as t2 
                   Group by t2.date) as t3 on t3.date = t1.date and t3.min_price = t1.price

get id of min value, grouped by another column

2 Answers2