Home > Database > Mysql Tutorial > How to Find the Latest Destination for Each Train Using SQL?

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

Linda Hamilton
Release: 2025-01-22 07:31:08
Original
625 people have browsed it

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:

<code class="language-sql">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</code>
Copy after login

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!

source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Latest Articles by Author
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template