SQL Server max timestamp per day for specific column from joined table

Question

I'm really new to SQL and have essentially googled my way to this point, I'm pretty stuck now.. so I hope someone out there can help!

Goal: get the rows with the most recent timestamp per day for every trip_id in MS SQL Server (there are multiple tables that need to be joined to get the data needed).

So each trip_id should have 1 row per day, like so… [removed some columns for readability]

timestamp,trip_id,stop_id,stop_code,arrival_time,departure_delay
4/28/2017 18:29,8888922,2847,52818,11:02:34,0
4/27/2017 18:26,8888922,2847,52818,11:02:34,60
4/25/2017 18:27,8888922,2847,52818,11:02:34,-120
4/28/2017 18:56,8888922,2847,52818,11:32:34,-60
4/25/2017 18:59,8888922,2847,52818,11:32:34,120
4/28/2017 19:34,8888922,2847,52818,12:02:34,360
4/27/2017 19:31,8888922,2847,52818,12:02:34,540
4/25/2017 19:27,8888922,2847,52818,12:02:34,-120

However right now the best I've been able to do is get the maximum timestamp for each day and departure_delay with the following query

select
max(trip_updates.timestamp) as max, stop_times.trip_id, stops.stop_id, stops.stop_code, stop_times.arrival_time, trips.service_id,
stops.stop_name, stop_times.shape_dist_traveled, stop_time_updates.departure_delay
from stops
inner join stop_times on stops.stop_id = stop_times.stop_id
inner join trips on trips.trip_id = stop_times.trip_id
inner join routes on trips.route_id = routes.route_id
inner join trip_updates on stop_times.trip_id = trip_updates.trip_id
inner join stop_time_updates on trip_updates.oid = stop_time_updates.trip_update_id
where
stop_code = '52818'
and service_id = '1'
and stop_times.arrival_time between '11:00%' and '14:00%'
and route_short_name = '134'
group by stop_times.trip_id, stops.stop_id, stops.stop_code, stop_times.arrival_time, trips.service_id,
stops.stop_name, stop_times.shape_dist_traveled, stop_time_updates.departure_delay
order by stop_times.arrival_time asc, max(trip_updates.timestamp) desc

Which is giving me results like...

timestamp,trip_id,stop_id,stop_code,arrival_time,departure_delay
4/28/2017 18:29,8888922,2847,52818,11:02:34,0
4/28/2017 18:21,8888922,2847,52818,11:02:34,30
4/28/2017 18:16,8888922,2847,52818,11:02:34,60
4/28/2017 18:11,8888922,2847,52818,11:02:34,120
4/27/2017 18:26,8888922,2847,52818,11:02:34,60
4/27/2017 18:22,8888922,2847,52818,11:02:34,30
4/27/2017 18:20,8888922,2847,52818,11:02:34,0

All help is appreciated! Thank you!

You either need to select fewer columns (such that you don't have to group by each individual column), or use a ROW_NUMBER() method (partitioning by trip_id and ordering by your timestamp desc). — ZLK, May 01 '17 at 23:36
Maybe I'm doing it wrong, but now I simply get row #'s for each row and every entry per day. `select max(trip_updates.timestamp) as max, ROW_NUMBER() over(partition by max(trip_updates.timestamp) order by stop_times.trip_id) as row, stops.stop_id,...` — Brad T, May 02 '17 at 00:19

score 0 · Accepted Answer · answered May 02 '17 at 00:25

As mentioned in my comment, one way to achieve this if you need to select all your current columns would be using the ROW_NUMBER() window function and removing your GROUP BY. For example,

SELECT [Max] = [timestamp], trip_id, stop_id, stop_code, arrival_time, service_id, stop_name, shape_dist_traveled, departure_delay
FROM
(
    SELECT trip_updates.[timestamp], stop_times.trip_id, stops.stop_id, stops.stop_code, stop_times.arrival_time, 
           trips.service_id, stops.stop_name, stop_times.shape_dist_traveled, stop_time_updates.departure_delay,
           RN = ROW_NUMBER() OVER (PARTITION BY stop_times.trip_id, CAST(trip_updates.[timestamp] AS DATE) ORDER BY trip_updates.[timestamp] DESC) 
           -- This assigns a row number for each row within each trip_id and each day, where a row number of 1 will be the highest timestamp.
    FROM ... 

    <put all your current joins / where clauses here>

    AND route_short_name = '134'
    -- Note: no GROUP BY
) AS T
WHERE RN = 1 -- This ensures you select only the the first row for each trip_id.
ORDER BY arrival_time, [timestamp];

I realized there is a flaw in the data that is going to force me instead to compare the timestamp (datetime) field to arrival_time (varchar). The timestamp field is UTC time and arrival_time is -07:00 utc. Could you please assist in modifying this so that instead of giving me the max timestamp it gives me the closest timestamp to arrival_time? — Brad T, May 07 '17 at 03:58
The easiest thing to do would probably be to change any time you're using arrival_time to `DATEADD(HOUR, 7, arrival_time)` or something like that (-7 on the timestamp column would also work, if you want to use local time instead of UTC time). — ZLK, May 08 '17 at 01:55

LordBaconPants · Answer 2 · 2017-05-02T01:42:10.250

You can join onto a subquery which has what you want:

SELECT  last_trip.timestamp AS max ,
        stop_times.trip_id ,
        stops.stop_id ,
        stops.stop_code ,
        stop_times.arrival_time ,
        trips.service_id ,
        stops.stop_name ,
        stop_times.shape_dist_traveled ,
        stop_time_updates.departure_delay
FROM    stops
        INNER JOIN stop_times ON stops.stop_id = stop_times.stop_id
        INNER JOIN trips ON trips.trip_id = stop_times.trip_id
        INNER JOIN routes ON trips.route_id = routes.route_id

        -- instead of joining directly to trip_updates, join to query
        -- that will number the entries per day
        INNER JOIN ( SELECT * ,
                            ROW_NUMBER() OVER ( PARTITION BY trip_id ORDER BY timestamp DESC ) AS rownum
                     FROM   trip_updates
                   ) AS last_trip ON stop_times.trip_id = last_trip.trip_id and rownum = 1 -- and join on the latest

        INNER JOIN stop_time_updates ON trip_updates.oid = stop_time_updates.trip_update_id
WHERE   stop_code = '52818'
        AND service_id = '1'
        AND stop_times.arrival_time BETWEEN '11:00%'
                                    AND     '14:00%'
        AND route_short_name = '134'
GROUP BY stop_times.trip_id ,
        stops.stop_id ,
        stops.stop_code ,
        stop_times.arrival_time ,
        trips.service_id ,
        stops.stop_name ,
        stop_times.shape_dist_traveled ,
        stop_time_updates.departure_delay
ORDER BY stop_times.arrival_time ASC ,
        MAX(trip_updates.timestamp) DESC;

This appears to have the same result as my query, I get max timestamp per day, but every departure_delay variant has its own row instead of giving me just the last timestamp for each day per trip_id — Brad T, May 02 '17 at 00:47
@BradT Ah, I missed a `and rownum = 1` on the subquery join. Sorry for that! — LordBaconPants, May 02 '17 at 01:42

SQL Server max timestamp per day for specific column from joined table

2 Answers2