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.

     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' 
     spreads.game_id, sportsbook_id, spread_type, spread_duration 
    spread_duration, spread_type, sportsbook_id, spreads.game_id,
    game_update_count DESC;

Anyone have any thoughts on a better approach to take?

2 Answers 2


The simplest way in Postgres is with DISTINCT ON:

       sp.game_id, sportsbook_id, spread_type, spread_duration, game_update_count
FROM   spreads sp
LEFT   JOIN schedule sch USING (game_id)
WHERE  date >= '2012-01-01'
AND    date <= '2012-01-02'
ORDER  BY 4,3,2,1, game_update_count DESC;


The numbers are just syntax shorthand referring to the ordinal position of SELECT items.

If game_update_count can be NULL, you'll want game_update_count DESC NULLS LAST.

  • Upvoted based on code readability. Erwin, do you know if Microsoft offers DISTINCT ON? This is the first I've seen it and I'm having some trouble finding documentation on it. Jan 21, 2015 at 19:13
  • 1
    @MarkIannucci: No, AFAIK, this is not available in SQL Server or MS Access. It's a Postgres extension to the standard SQL DISTINCT clause. Jan 21, 2015 at 19:17
  • This example makes it look like the DISTINCT ON items need to be in the reverse order of the ORDER BY items, but that's not the case, is it?
    – Andy
    Apr 3, 2020 at 19:26
  • @Andy: No, items don't have to be in reverse order. I just kept the sort order of the original query. Leading ORDER BY expressions must match the set of expressions in DISTINCT ON, but we are free to rearrange order within that set. I clarified the explanation in my linked answer as that wasn't clear before. Apr 3, 2020 at 20:04

Instead of using a group by, have you tried using a Window Function?

Try this:

       spreads.game_id, sportsbook_id, spread_type, spread_duration,
       row_number() over (partition by spreads.game_id, sportsbook_id, spread_type, spread_duration order by update_time desc) priority
    FROM spreads 
    LEFT JOIN schedule ON 
        schedule.game_id = spreads.game_id 
    WHERE date >= '2012-01-01' AND date <= '2012-01-02'
WHERE priority = 1

The row_number() which I've aliased as priority is the window function and what it is doing is conceptually similar to a GROUP BY clause. The difference is that it is just allowing you to still see the data with row-level fidelity through the window. In this case, you'll notice that the columns you grouped by have been used to partition the data. The outer SELECT statement eliminates the data that you don't want (all of the out of date sport-bet lines).

I wish you the best as you implement Window Functions (and with your sport betting)!

(word of warning... I typically work with SQL Server guy, so my code may not by syntactically correct, but it should get you off to a good start)

  • Thank you for this. I had looked into window functions but hit a wall. Your explanation makes sense, appreciate it!
    – Jeremy
    Jan 22, 2015 at 0:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.