I've got a SQL table that contains train agendas. The table looks something similar to this:

Schedule

TrainNumber
LegID
DepartureTime
DepartureStation
ArrivalTime
ArrivalStation

My real database contain several tables, however for this just the one above is pertinent. Different trainNumber might have different quantity of legs. With different departure station selected with a user, I wish to output all approaching routes from that station.

The output must contain Departure some time and Arrival station. But I'd rather not range from the legs among. Can anybody guide me within the right direction how I'm able to accomplish this? I attempted utilizing a max statement. But did not quite get results generate an income desired to.

Also, there might be multiple departures through the same train number on the day that.

You should make use of the combination (DepartureTime + TrainNumber) because the answer to your query, get the most arrival time considering that mixture of values, after which discover exactly what the corresponding ArrivalStation is. So you could do this an inner join between your Schedule along with a arranged version of itself, i.e.

SELECT
    TrainTableA.TrainNumber
    ,TrainTableA.DepartureTime
    ,ArrivalStation
FROM
    (SELECT /* all upcoming train routes for given station */
        TrainNumber
        ,DepartureTime
        ,ArrivalTime
        ,ArrivalStation
    FROM
        Schedule
    WHERE DepartureStation = givenStation
    ) as TrainTableA
    INNER JOIN 
    (SELECT /* Just the last station for each departure */
        TrainNumber
        ,DepartureTime
        ,Max(ArrivalTime) as a
    FROM
        Schedule
    GROUP BY
          TrainNumber
          ,DepartureTime
    ) as TrainTableB
    ON
    TrainTableA.TrainNumber = TrainTableB.TrainNumber
    AND TrainTableA.DepartureTime = TrainTableB.DepartureTime
    AND TrainTableA.ArrivalTime = TrainTableB.a

I can not quite tell in the question for those who have a universal indicator from the route sequence, and so i used max(ArrivalTime). You might use max(LegID) if each LegID is more than the main one before it. Also, I believed the ArrivalTime includes the date, so 1:00 AM on the following day continues to be after 10:00 PM on the day that. So, obviously, adapt to taste.