How to Retrieve the Latest Record for Each Group Using SQL's GROUP BY and MAX(DATE)?

Group using GROUP BY and MAX(DATE) [duplicate]

In SQL, you may encounter situations where you need to extract the latest records based on a specific column and group by another column. For example, you might need to determine the latest destination of each train in the table.

Group by MAX(Time)

Initially, you tried to achieve this using a simple GROUP BY statement and MAX(Time), but this resulted in an error. This is because you cannot aggregate non-grouping columns in a GROUP BY statement. To fix this problem you have to include 'Dest' in the group by statement. However, this does not produce the expected results.

Use subquery and RANK function

The solution involves using subqueries and the RANK() function. The subquery creates an intermediate table that calculates the ranking of each destination based on departure time. The RANK() function assigns rankings based on time, and for each train, the maximum value (latest time) is assigned a rank of 1.

The final query selects the train, destination and time from the subquery where dest_rank is 1. This ensures that only the latest destination of each train is fetched.

The following is the updated SQL query:

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;

This query will produce the desired results:

火车	目的地	时间
1	SZ	14:00
2	HK	13:00

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