Too many order by, max, subqueries for my intellect

I can not seem to work out this query. I'm sure it needs subqueries, but I am out of options. My brain can not handle this or something. I need help :)

Little intro

I have a betting odds website. Every 15 minutes I import the latest odds (win/draw/lose -- or 1/X/2) for particular events from various bookmakers.

Every row of the odds table has the odds_type ('1', 'X' or '2'), the odds_index which is the actual odds, the bookmaker_id and the event_id.

But equally important: created_at, because I need to work with the odds from the latest import. It is very important for obvious reasons.

Scenario

In the table below we are working with imported odds for event_id #1.

Needed query
  1. Isolate the latest set of imported odds for event_id = 1 from all bookmakers (in this example 9 records)
  2. On that set, return the highest odds_index record for odds_type '1', 'X', '2'.

Now I prefer to do this with Rails scoping, so I can use @event.best_odds1 and @event.best_odds2, but I'll take any approach if it works. Been busting my brain on this for 5 days. Need to solve it.

Result

After the query I end up with 3 records so I can display "The best odds for event #1".

The tables

Bookmakers

 ID  |  NAME
 ----------------------
 1   |  Unibet
 2   |  888
 3   |  Ladbrokes

Events

 ID  |  NAME
 --------------------------
 1   |  Holland vs Denmark
 2   |  England vs Germany
 3   |  France vs Spain

Odds

 ID  |  OT  |  OI  |  BI  |  EI  |  CREATED_AT
 ---------------------------------------------------
 (first import from the bookies)
 1   |  '1'  |  2.4  |  1  |  1  |  2010-06-10 15:00
 2   |  'X'  |  1.5  |  1  |  1  |  2010-06-10 15:00
 3   |  '2'  |  6.2  |  1  |  1  |  2010-06-10 15:00
 4   |  '1'  |  2.2  |  2  |  1  |  2010-06-10 15:58
 5   |  'X'  |  1.8  |  2  |  1  |  2010-06-10 15:58
 6   |  '2'  |  5.2  |  2  |  1  |  2010-06-10 15:58
 7   |  '1'  |  2.8  |  3  |  1  |  2010-06-10 16:56
 8   |  'X'  |  1.3  |  3  |  1  |  2010-06-10 16:56
 9   |  '2'  |  7.1  |  3  |  1  |  2010-06-10 16:56
 (last import from the bookies)
 10  |  '1'  |  2.5  |  1  |  1  |  2010-06-11 17:10
 11  |  'X'  |  1.3  |  1  |  1  |  2010-06-11 17:10
 12  |  '2'  |  6.4  |  1  |  1  |  2010-06-11 17:10
 13  |  '1'  |  2.1  |  2  |  1  |  2010-06-11 18.12
 14  |  'X'  |  1.2  |  2  |  1  |  2010-06-11 18:58
 15  |  '2'  |  6.2  |  2  |  1  |  2010-06-11 18:58
 16  |  '1'  |  1.8  |  3  |  1  |  2010-06-12 14:56
 17  |  'X'  |  2.3  |  3  |  1  |  2010-06-12 14:56
 18  |  '2'  |  5.1  |  3  |  1  |  2010-06-12 14:56

Abbreviated column names to fit on screen

OT = odds_type
OI = odds_index 
BI = bookmaker_id 
EI = event_id

Answers


You could use row_number() twice:

select  *
from    (
        select  *
        ,       row_number() over (partition by OT order by OI desc) as rn2
        from    (
                select  *
                ,       row_number() over (partition by EI, BI, OT 
                                           order by created_at desc) as rn1
                from    Odds
                where   EI = 1 -- for event 1
                ) sub1
        where   rn1 = 1 -- Latest row per EI, BI, OT
        ) sub2
where   rn2 = 1 -- Highest OI per OT

But if the table keeps growing, this will perform badly. You could add a history table like OddsHistory, and move outdated Odds there. When only the latest Odds are in the Odds table, your query becomes much simpler.

Live example at SQL Fiddle.


Need Your Help

C++/CX and C++/CLI dependency

c++-cli c++-cx

What is the relationship between C++/CX and C++/CLI from the syntaxes and compiling process view?

Creating directories from filename?

php image codeigniter directory storage

I'm creating website that handles "great amount" of images. I have decided to use multiple directories method for storing files eg. images/efg/ed/ehj/efgedehjokjvdf2jn4.jpg