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>
Query works as follows:
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!