MySQL Forums
Forum List  »  Optimizer & Parser

Re: select query optimize
Posted by: Robert Koziol
Date: February 14, 2011 06:26AM

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...

Options: ReplyQuote


Subject
Views
Written By
Posted
3104
February 10, 2011 05:14AM
1404
February 12, 2011 06:56PM
Re: select query optimize
1365
February 14, 2011 06:26AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.