Sql join to table based on most recent date and time

Question

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;

Please either ask about 1 bad query/function with the obligatory [mre] & why you think it should return something else at the 1st subexpression that it doesn't give what you expect justified by reference to authoritative documentation or ask about your overall goal giving working parts you can do & ideally a [mre]. But please ask about the former 1st because misconceptions in the former will get in the way of understanding the latter. And bad code doesn't tell us what you wish it would do. [ask] [Help] — philipxy, Aug 25 '22 at 22:21
[Fetch the row which has the Max value for a column](https://stackoverflow.com/q/121387/3404097) [Select first row in each GROUP BY group?](https://stackoverflow.com/q/3800551/3404097) [Retrieving the last record in each group - MySQL](https://stackoverflow.com/q/1313120/3404097) etc etc — philipxy, Aug 25 '22 at 22:23
Please before considering posting: Pin down code issues via [mre]. Read the manual/reference & google any error message & many clear, concise & precise phrasings of your question/problem/goal, with & without your particular names/strings/numbers, 'site:stackoverflow.com' & tags; read many answers. Reflect research in posts. SO/SE search is poor & literal & unusual, read the help. Google re googling/searching, including Q&A at [meta] & [meta.se]. [How much research effort is expected of Stack Overflow users?](https://meta.stackoverflow.com/q/261592/3404097) [ask] [Help] — philipxy, Aug 25 '22 at 22:23
To express it in this way (joining on a correlated subquery) you need to use LATERAL expressions. I don't use DB2, so I'll let you look up how IBM chose to implement those. — MatBailie, Aug 25 '22 at 22:32
Why do you think it means anything to write t JOIN (table defined in terms of a row from t)? There's no single table value to calculate as the right argument of the join. (There is a notion of LATERAL JOIN that does use a table per row in a certain way.) — philipxy, Aug 25 '22 at 22:34

DannySlor · Accepted Answer · 2022-08-26T01:56:00.560

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

score 0 · Answer 2 · answered Aug 26 '22 at 07:10

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

Sql join to table based on most recent date and time

2 Answers2