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

Use MAX(DATE) and GROUP BY clause to find the latest destination

The goal of this SQL query is to retrieve the latest destination of each train, subject to the maximum departure time. However, when excluding the 'Dest' column from the GROUP BY clause, I encounter the error "ora-00979 not a GROUP BY expression", which seems counter-intuitive.

To solve this problem, we can utilize the RANK() window function to assign a ranking to each destination within each train group. Specifically, the highest ranked destination will correspond to the latest departure time.

The following SQL query demonstrates this approach:

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

Query works as follows:

1. The inner subquery creates an intermediate result set that includes the train, destination, departure time, and the ranking of each destination within each train group. The RANK() function assigns rankings in descending order of departure time, with the highest ranking (1) indicating the latest departure time.
2. The external query selects records with destination ranking equal to 1. This effectively filters out all but the rows representing each train's latest destination.
3. The final result shows the train, latest destination and corresponding departure time for each train.

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