Grouping and filtering by newest timestamp

Question

I have a table like this:

-------------------------------------------------
id   | race_id | heat | updated_at                
-------------------------------------------------
28952   189        1    2017-06-24 19:57:44.433431
28956   189        1    2017-06-24 20:33:49.984958
28958   189        1    2017-06-24 21:43:59.538041
28953   189        2    2017-06-24 19:57:45.309102
28957   189        2    2017-06-24 20:36:41.725912
28961   189        2    2017-06-24 21:46:39.677080
28954   189        3    2017-06-24 19:57:46.142353
28959   189        3    2017-06-24 21:44:00.651232
28964   189        3    2017-06-24 23:13:24.172765
28969   189        3    2017-06-25 01:01:25.445597
28955   189        4    2017-06-24 19:57:47.056272
28962   189        4    2017-06-24 21:46:40.839643
28965   189        4    2017-06-25 01:00:44.736679
28966   189        4    2017-06-25 01:01:04.010346
28960   189        5    2017-06-24 21:44:02.443140
28963   189        5    2017-06-24 21:46:41.861473
28968   189        6    2017-06-25 01:01:06.028000
28971   189        6    2017-06-25 01:01:28.445862
28967   189        8    2017-06-25 01:01:05.198073
28970   189        8    2017-06-25 01:01:27.533253

But I am trying to group by race_id and heat with the newest time stamp for each group. I expect to see something like this:

-------------------------------------------------
id   | race_id | heat | updated_at                
-------------------------------------------------
28958   189       1     2017-06-24 21:43:59.538041
28961   189       2     2017-06-24 21:46:39.677080
28969   189       3     2017-06-25 01:01:25.445597
28966   189       4     2017-06-25 01:01:04.010346
28963   189       5     2017-06-24 21:46:41.861473
28971   189       6     2017-06-25 01:01:28.445862
28970   189       8     2017-06-25 01:01:27.533253

I am trying to use

select
    id,
    race_id,
    heat,
    updated_at
from 
    heats
where
    race_id = 189
group by
    race_id,
    heat
having
    updated_at = max(updated_at)
order by
    heat asc;

but I am not getting any result back.

last_updated = max(last_updated) ? You said you have `updated_at` prop — Maksim Nesterenko, Jul 04 '17 at 21:12

score 0 · Answer 1 · answered Jul 04 '17 at 21:13

0

No, group by is the wrong approach. Simply use a comparison to the most recent time stamp:

select h.*
from heats h
where h.race_id = 189 and
      h.last_updated = (select max(h2.last_updated)
                        from heats
                        where h2.race_id = h.race_id and h2.heat = h.heat
                       )
order by heat asc;

answered Jul 04 '17 at 21:13

Gordon Linoff

1,242,037
58
646
786

Why is `group by` the wrong approach? – Aaron Jul 04 '17 at 21:18
@Aaron . . . Because you want to select one particular row, not aggregate the data. – Gordon Linoff Jul 04 '17 at 22:21

Grouping and filtering by newest timestamp

1 Answers1