How to Get the Latest Destination for Each Train Using SQL's `GROUP BY` and `MAX(DATE)`?

SQL query using GROUP BY and MAX(DATE) [Duplicate question]

Question:

Get the latest destination of each train based on its maximum departure time from the given table. The desired output should exclude duplicate destinations while retaining the latest time.

Sample data:

<code>火车    目的地      时间
1        HK        10:00
1        SH        12:00
1        SZ        14:00
2        HK        13:00
2        SH        09:00
2        SZ        07:00</code>

Expected result:

<code>火车    目的地      时间
1        SZ        14:00
2        HK        13:00</code>

Initial attempt:

Use simple GROUP BY query and MAX(Time):

<code class="language-sql">SELECT Train, Dest, MAX(Time)
FROM TrainTable
GROUP BY Train</code>

Error:

This query results in the error "ora-00979 not a GROUP BY expression" indicating that the Dest column must also be included in the GROUP BY statement. However, this will result in a duplicate destination for each train.

Solution:

To achieve the desired results, more complex queries can be used:

<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>

This query first uses the RANK function to calculate the ranking of each train for each destination in descending order of departure time. The PARTITION BY train clause ensures that ranking is performed within each train group. Finally, the query only retrieves records where dest_rank equals 1, effectively filtering out duplicate destinations and retaining only the latest destination for each train.

The above is the detailed content of How to Get the Latest Destination for Each Train Using SQL's `GROUP BY` and `MAX(DATE)`?. For more information, please follow other related articles on the PHP Chinese website!