get id of min value, grouped by another column [duplicate]

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

Answer 1

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;