Re: select query optimize

[Robert Koziol]

A bit of explanation:

I have sport events and bookmaker's odds for each event. The odds of each bookmaker may change in time. For example:

A football match between Liverpool and Barcelona
First bookmaker's odds: Liv. 3.30 draw 3.00 Bar. 2.00
but after some time the first bookmaker's odds changed to Liv. 3.40 draw 3.00 Bar. 1.90
Second bookmaker's odds: Liv. 3.20 draw 3.10 Bar. 2.00
but after some time the first bookmaker's odds changed to Liv. 3.50 draw 3.00 Bar. 1.80
Third bookmaker's odds: Liv. 3.10 draw 3.05 Bar. 2.10
Third bookmaker's odds have not changed at all.

I would like to select events with the corresponding:
- max odds for the first competitor of the event (Liverpool) with a name of the bookmaker which gives these odds
- max odds for the draw with the appropriate bookmaker's name
- max odds for the second competitor (Barcelona) with the appropriate bookmaker's name

If the odds change in time (in our example the first and the second bookmaker's odds) we should take only the latest odds (with max insert_date).

So the result should be:

1234 (event_id) 3.50 second_bookmaker 3.05 third_bookmaker 2.10 third_bookmaker
1235 (some other event id) ... ...

Notice that the second bookmaker had given the max odds for the draw (3.10) - but then it changed to 3.00 which is less than the current 3.05 of the third bookmaker - and we are interested in the last odds of each bookmaker.

"group by je.id having count(je.id)>=1" is probably redundant indeed but I have no idea how to use UNION with this query...