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?
This question is related to
sql
oracle
group-by
greatest-n-per-group
ora-00979
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
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
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;
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
Source: Stackoverflow.com