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

User · Accepted Answer

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

User · Answer

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

User · Answer

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  lt  t2 Time  WHERE t2 Time IS NULL

User · Answer

Another solution   select   from traintable where  train  time  in  select train  max time  from traintable group by train

User · Answer

I know I m late to the party  but try this     SELECT       Train         Dest       SUBSTRING INDEX GROUP CONCAT  Time  ORDER BY  Time  DESC        1  AS  Time  FROM TrainTable GROUP BY Train    Src  Group Concat Documentation  Edit  fixed sql syntax

User · Answer

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

[sql] GROUP BY with MAX(DATE)

Examples related to sql

Examples related to oracle

Examples related to group-by

Examples related to greatest-n-per-group

Examples related to ora-00979