SQL for how to Get next stations based on station_id in GTFS data

This is my SQL query

SELECT 
Routes.route_id, Routes.route_desc, Routes.route_type, 
Trips.service_id, Trips.trip_id, Trips.route_direction, 
Stop_times.stop_id, Stop_times.arrival_time, Stop_times.departure_time, 
Stops.stop_name, Calendar.start_date, Calendar.end_date
FROM Trips 
INNER JOIN Routes ON Trips.route_id = Routes.route_id
INNER JOIN Stop_times ON Trips.trip_id = Stop_times.trip_id 
INNER JOIN Stops ON Stop_times.stop_id = Stops.stop_id
INNER JOIN Calendar ON Trips.service_id = Calendar.service_id
WHERE 
Calendar.start_date = strftime('%Y%m%d','now') and
CAST(REPLACE(Stop_times.arrival_time, ':', '') as decimal)  
         >= strftime('%H%M%S', 'now', 'localtime')
and Trips.direction_id = 0
and Stop_times.stop_id = '279018' 
ORDER BY Stop_times.arrival_time asc

1> I have resulted as like this

(many other columns as well)

2>I want to use the first-row trip_id and put that as

SELECT 
Routes.route_id, Routes.route_desc, Routes.route_type, 
Trips.service_id, Trips.trip_id, Trips.route_direction, 
Stop_times.stop_id, Stop_times.arrival_time, Stop_times.departure_time, 
Stops.stop_name, Calendar.start_date, Calendar.end_date
FROM Trips 
INNER JOIN Routes ON Trips.route_id = Routes.route_id
INNER JOIN Stop_times ON Trips.trip_id = Stop_times.trip_id 
INNER JOIN Stops ON Stop_times.stop_id = Stops.stop_id
INNER JOIN Calendar ON Trips.service_id = Calendar.service_id
WHERE 
Calendar.start_date = strftime('%Y%m%d','now') and
CAST(REPLACE(Stop_times.arrival_time, ':', '') as decimal)  
         >= strftime('%H%M%S', 'now', 'localtime')
and Trips.direction_id = 0
and Stop_times.trip_id = 'what ever on first row from above query example 551.22018xxxxx' 
ORDER BY Stop_times.arrival_time asc

The result should be as following

Thanks in Advance.

Solution

You can use subquery to wrap your result and use the result to execute your filter.

Your query should be like this :

SELECT *
FROM(
SELECT 
    Routes.route_id, Routes.route_desc, Routes.route_type, 
    Trips.service_id, Trips.trip_id, Trips.route_direction, 
    Stop_times.stop_id, Stop_times.arrival_time, Stop_times.departure_time, 
    Stops.stop_name, Calendar.start_date, Calendar.end_date
 FROM Trips 
    INNER JOIN Routes ON Trips.route_id = Routes.route_id
    INNER JOIN Stop_times ON Trips.trip_id = Stop_times.trip_id 
    INNER JOIN Stops ON Stop_times.stop_id = Stops.stop_id
    INNER JOIN Calendar ON Trips.service_id = Calendar.service_id
 WHERE 
    Calendar.start_date = strftime('%Y%m%d','now') and
    CAST(REPLACE(Stop_times.arrival_time, ':', '') as decimal)  
             >= strftime('%H%M%S', 'now', 'localtime')
    and Trips.direction_id ={}
    and Stops_Time.Stop_ID = 'xxxx' 
ORDER BY Stop_times.arrival_time ASC)
WHERE Trip_ID = @Trip_ID

EDIT :

You need just 1 row the first in the Stop_Times.Stop_ID so wrap the same condition with your select first and add limit

 SELECT 
    Routes.route_id, Routes.route_desc, Routes.route_type, 
    Trips.service_id, Trips.trip_id, Trips.route_direction, 
    Stop_times.stop_id, Stop_times.arrival_time, Stop_times.departure_time, 
    Stops.stop_name, Calendar.start_date, Calendar.end_date
 FROM Trips 
    INNER JOIN Routes ON Trips.route_id = Routes.route_id
    INNER JOIN Stop_times ON Trips.trip_id = Stop_times.trip_id 
    INNER JOIN Stops ON Stop_times.stop_id = Stops.stop_id
    INNER JOIN Calendar ON Trips.service_id = Calendar.service_id
 WHERE 
    Calendar.start_date = strftime('%Y%m%d','now') and
    CAST(REPLACE(Stop_times.arrival_time, ':', '') as decimal)  
             >= strftime('%H%M%S', 'now', 'localtime')
    and Trips.direction_id ={}
    and Stop_Times.Trip_ID = 
        (SELECT Stop_Times.Trip_ID
         FROM Trips
            INNER JOIN Stop_times ON Trips.trip_id = Stop_times.trip_id 
            INNER JOIN Stops ON Stop_times.stop_id = Stops.stop_id
            INNER JOIN Calendar ON Trips.service_id = Calendar.service_id
          WHERE
            Calendar.start_date = strftime('%Y%m%d','now') and
            CAST(REPLACE(Stop_times.arrival_time, ':', '') as decimal)  
                   >= strftime('%H%M%S', 'now', 'localtime')
            and Trips.direction_id ={}
          ORDER BY Stop_times.arrival_time ASC LIMIT 1) 
 ORDER BY Stop_times.arrival_time ASC