unable to retrieve the latest row based on a date

Question

Imagine the following 3 tables:

train: key, name, departure_date
group_train: train, group
group: key, name

The group_train.train as a foreign key relation with train.key and group_train.group with group.key.

A group can contain multiple trains and a train might be present in multiple groups.

I would like to retrieve the latest train to departure by group. Based on the train.departure_date

I already tried multiple types of joins, sub queries and group by clauses. All of them without success. I seems a straightforward query but for some reason I got stuck!

Thank you for your help!

Answer 1

Analytical function Approach;

SELECT train.*
FROM
    (
     SELECT T.KEY,
            T.NAME AS TRAIN_NAME,
            T.departure_date,
            G.name AS GROUP_NAME,
            RANK() OVER (PARTITION BY G.group ORDER BY T.departure_date DESC) AS MY_RANK
     FROM GROUP_TRAIN GT
      INNER JOIN TRAIN T ON (GT.TRAIN = T.KEY)
      INNER JOIN GROUP G ON (GT.group = G.group)
   ) train
WHERE MY_RANK   = 1