I have a table like the following:
spread_id serial NOT NULL,
game_id integer NOT NULL,
sportsbook_id integer NOT NULL,
spread_type integer NOT NULL,
spread_duration integer NOT NULL,
home_line double precision,
home_odds integer,
away_line double precision,
away_odds integer,
update_time timestamp without time zone NOT NULL,
game_update_count integer NOT NULL
I'm trying to get the last row inserted (max update_time or game_update_count), for each group of sportsbook_id, spread_type, spread_duration, and game_id.
The following query gets me close, but I am not able to select the lines/odds without Postgres complaining.
SELECT
spreads.game_id, sportsbook_id, spread_type, spread_duration,
MAX(game_update_count) AS game_update_count
FROM spreads
LEFT JOIN schedule ON
schedule.game_id = spreads.game_id
WHERE date >= '2012-01-01' AND date <= '2012-01-02'
GROUP BY
spreads.game_id, sportsbook_id, spread_type, spread_duration
ORDER BY
spread_duration, spread_type, sportsbook_id, spreads.game_id,
game_update_count DESC;
Anyone have any thoughts on a better approach to take?