Максимальная отметка времени SQL Server в день для определенного столбца из объединенной таблицы

Как упоминалось в моем комментарии, одним из способов добиться этого, если вам нужно выбрать все текущие столбцы, будет использование ROW_NUMBER() оконной функции и удалив файл GROUP BY. Например,

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]; 

Вы можете присоединиться к подзапросу, который имеет то, что вы хотите:

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;