0

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.

hldev
  • 914
  • 8
  • 18

1 Answers1

0

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);
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786