GROUP BY with MAX(DATE) [duplicate]
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.MaxTimeFROM ( SELECT Train, MAX(Time) as MaxTime FROM TrainTable GROUP BY Train) rINNER JOIN TrainTable tON 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 t2ON (t1.Train = t2.Train AND t1.Time < t2.Time)WHERE t2.Time IS NULL;