How to Find the Latest Destination and Departure Time for Each Train Using SQL?

Use MAX(Date) in GROUP BY query

Question

Given a table containing train schedule information such as train number, destination and departure time, how do you find the latest destination of each train and its departure time in a single line of SQL?

Answer

To do this, you can use a ranking function combined with a subquery:

SELECT train, dest, time
FROM (
  SELECT train, dest, time,
    RANK() OVER (PARTITION BY train ORDER BY time DESC) dest_rank
  FROM traintable
)
WHERE dest_rank = 1;

Segmentation:

• The inner subquery uses the RANK() function to create a ranked list of destinations by departure time for each train.
• The outermost query then selects the train, dest and time values ​​from the subquery with dest_rank equal to 1, effectively returning the latest destination of each train.

The above is the detailed content of How to Find the Latest Destination and Departure Time for Each Train Using SQL?. For more information, please follow other related articles on the PHP Chinese website!