How to select a row that has a column with the minimum value from a subset of rows?

Question

I have a table of events' outcome probabilities from different bookmakers, odds(id, event_id, bookmaker_id, market_id, value), from where I want to select the lowest odd for all markets. I came up with

SELECT * FROM odds WHERE value IN (SELECT MIN(value) FROM odds GROUP BY market_id)

But that's wrong because the IN filter may compare values that does not belong to the current row.

Answer 1

Use a correlated subquery:

SELECT o.*
FROM odds o
WHERE o.value = (SELECT MIN(o2.value) FROM odds o2 WHERE o2.market_id = o.market_id);