I have these tables in a PostgreSQL database:
bookmakers
-----------------------
| id | name |
-----------------------
| 1 | Unibet |
-----------------------
| 2 | 888 |
-----------------------
odds
---------------------------------------------------------------------
| id | odds_type | odds_index | bookmaker_id | created_at |
---------------------------------------------------------------------
| 1 | 1 | 1.55 | 1 | 2012-06-02 10:30 |
---------------------------------------------------------------------
| 2 | 2 | 3.22 | 2 | 2012-06-02 10:30 |
---------------------------------------------------------------------
| 3 | X | 3.00 | 1 | 2012-06-02 10:30 |
---------------------------------------------------------------------
| 4 | 2 | 1.25 | 1 | 2012-05-27 09:30 |
---------------------------------------------------------------------
| 5 | 1 | 2.30 | 2 | 2012-05-27 09:30 |
---------------------------------------------------------------------
| 6 | X | 2.00 | 2 | 2012-05-27 09:30 |
---------------------------------------------------------------------
What I am trying to query is the following:
Give me the 1/X/2 odds from the latest update (created_at) from ALL bookmakers and from that last update, give me the highest odds for each odds_type ('1', '2', 'X').
On my website I display them as:
Best odds right now: 1 | X | 2
--------------------
2.30 | 3.00 | 3.22
I have to first get the latest, because the odds from the update from yesterday are no longer valid. Then from that last update, I have - in this case - 2 odds from 2 different bookmakers, so I need to get the best one for type '1','2','X'.
Pseudo SQL would be something like:
SELECT MAX(odds_index) WHERE odds_type = '1' ORDER BY created_at DESC, odds_index DESC
But that doesn't work, because I would always get the latest odds (and not the highest/best from those latest)
I hope I'm making sense.