GROUP BY with MAX(DATE)

Question

Hello everyone,

I have a table showing trains with their destination and departure time. My objective is to list the latest destination (MAX departure time) for each trains.

So for example the table is

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? (sub query is ok)

Thanks a lot.

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

Answer 2

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.

I think you want to know at which destination the train arrives latest. You will need a subselect. For example:

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

Answer 3

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;