sql - GROUP BY with MAX(DATE)

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 

You cannot include non-aggregated columns in your result set which are not grouped. If a train has only one destination, then just add the destination column to your group by clause, otherwise you need to rethink your query.

Try:

SELECT t.Train, t.Dest, r.MaxTime FROM (       SELECT Train, MAX(Time) as MaxTime       FROM TrainTable       GROUP BY Train ) r INNER JOIN TrainTable t ON t.Train = r.Train AND t.Time = r.MaxTime 

Here's an example that only uses a Left join and I believe is more efficient than any group by method out there: ExchangeCore Blog

SELECT t1.* FROM TrainTable t1 LEFT JOIN TrainTable t2 ON (t1.Train = t2.Train AND t1.Time < t2.Time) WHERE t2.Time IS NULL; 

Another solution:

select * from traintable where (train, time) in (select train, max(time) from traintable group by train); 

As long as there are no duplicates (and trains tend to only arrive at one station at a time)...

select Train, MAX(Time),       max(Dest) keep (DENSE_RANK LAST ORDER BY Time) max_keep from TrainTable GROUP BY Train;