GROUP BY with MAX(DATE)

greatest-n-per-group, group-by, ora-00979, oracle, sql

Solution

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

Problem

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?

Original source

Related problems