How to Find the Maximum Departure Time for Each Train Using SQL?

Use GROUP BY and MAX(DATE) to find the maximum departure time of each train

In a database environment, grouping records by specific columns allows you to perform aggregation operations on grouped data. In this example, the goal is to determine the latest destination (maximum departure time) associated with each train in the data table.

To do this, we can leverage the following SQL query:

<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 uses a subquery to create a derived table containing the train, destination, departure time, and the ranking of each train's departure time. The ranking function assigns a sequence number to each departure time within each train, with the latest time getting the highest ranking.

Then, the main query selects rows in the derived table with departure time ranking equal to 1. Effectively this only extracts the latest destination for each train, ensuring the result reflects the desired output:

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

This method allows you to determine the latest destination of each train using a single SQL statement, thus accomplishing the goal of your data analysis task.

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