Getting random and last value from group in SQL

Question

I have a SQL table containing train schedules. The table looks something like this:

Schedule

TrainNumber
LegID
DepartureTime
DepartureStation
ArrivalTime
ArrivalStation

My real database contain several tables, but for this question only the one above is relevant. Different trainNumber can have different amount of legs. Based on a departure station chosen by a user, I want to output all upcoming routes from that station.

The output must contain Departure time and Arrival station. But I don't want to include the legs in between. Can anyone guide me in the right direction on how I can achieve this? I tried using a max statement. But didn't quite get it to work the way I wanted to.

Also, there can be multiple departures by the same train number on the same day.

Answer 1

You would need to use the combination (DepartureTime + TrainNumber) as the key to your query, get the maximum arrival time given that combination of values, and then find out what the corresponding ArrivalStation is. So you could do an inner join between the Schedule and a grouped 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't quite tell from the question if you have a universal indicator of the route sequence, so I used max(ArrivalTime). You could also use max(LegID) if each LegID is greater than the one before it. Also, I assumed that The ArrivalTime includes the date, so 1:00 AM on the next day is still later than 10:00 PM on the same day. So, of course, adjust to taste.