GROUP BY with MAX(DATE)

Question

I'm trying to list the latest destination (MAX departure time) for each train in a table, for example:

Train    Dest      Time
1        HK        10:00
1        SH        12:00
1        SZ        14:00
2        HK        13:00
2        SH        09:00
2        SZ        07:00

The desired result should be:

Train    Dest      Time
1        SZ        14:00
2        HK        13:00

I have tried using

SELECT Train, Dest, MAX(Time)
FROM TrainTable
GROUP BY Train

by I got a "ora-00979 not a GROUP BY expression" error saying that I must include 'Dest' in my group by statement. But surely that's not what I want...

Is it possible to do it in one line of SQL?

Answer 1

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