Sql join to table based on most recent date and time-CodePudding

I want to write a SQL query joining two tables where I can see driver names and the most recent route's destination, arrive date, and arrive time.

drivers:

driver_num	name	home
1	Bob	Columbus

routes:

driver_num	destination	arrived_date	arrive_time
1	Columbus	1220825	1200
1	Indianapolis	1220825	1800
1	Columbus	1220826	1000
1	Indianapolis	1220826	1200

A driver can be assigned to several routes within a day.

So the query I want would return:

driver_num	name	destination	arrived_date	arrive_time
1	Bob	Indianapolis	1220826	1200

I've tried different joins and sub selects but nothing I try limits the trip data to the most recent route. This is the closest I've gotten so far but it doesn't work because the drivers.driver_num is not within the scope of the subselect:

select 
    driver_num,
    name,
    destination,
    arrived_date,
    arrived_time
from drivers d
join (
  select driver_num, destination, arrived_date, arrived_time
  from routes r
  where r.driver_num = d.driver_num
  order by arrived_date desc, arrived_time desc
  limit 1
) as most_resent_trip
on r.driver_num = most_resent_trip.driver_num
order by name desc;

CodePudding user response：

I used row_number in case you have more than one driver and you want the results from all of them in the same table.
I used SQL Server instead of DB2, but it's pretty simple so it should translate well.

  select  driver_num 
       ,name
       ,destination 
       ,arrived_date    
       ,arrive_time
from   (
        select routes.driver_num 
              ,routes.destination   
              ,routes.arrived_date  
              ,routes.arrive_time
              ,drivers.name
              ,row_number() over(partition by drivers.driver_num order by routes.arrived_date desc, routes.arrive_time desc) as rn
        from drivers join routes on routes.driver_num = drivers.driver_num
        where  drivers.home <> routes.destination
        ) t
where rn = 1

driver_num	name	destination	arrived_date	arrive_time
1	Bob	Indianapolis	1220826	1200

Fiddle

CodePudding user response：

The LATERAL (or TABLE clause instead can be used) use example.

/*
WITH
  drivers (driver_num, name, home) AS
(
  VALUES
    (1, 'Bob', 'Columbus')
)
, routes (driver_num, destination, arrived_date, arrive_time) AS
(
  VALUES
    (1, 'Columbus', 1220825, 1200),
    (1, 'Indianapolis', 1220825, 1800),
    (1, 'Columbus', 1220826, 1000),
    (1, 'Indianapolis', 1220826, 1200)
)
*/    
SELECT 
  r.driver_num 
, r.destination 
, r.arrived_date    
, r.arrive_time
, d.name
FROM drivers d
CROSS JOIN LATERAL 
(
  SELECT 
    r.driver_num 
  , r.destination   
  , r.arrived_date  
  , r.arrive_time
  FROM routes r
  WHERE r.driver_num = d.driver_num
  ORDER BY r.arrived_date DESC, r.arrive_time DESC
  FETCH FIRST 1 ROW ONLY
) r

DRIVER_NUM	DESTINATION	ARRIVED_DATE	ARRIVE_TIME	NAME
1	Indianapolis	1220826	1200	Bob