I have two tables that I need to join & aggregate.

Table 1 has 1 row per date & vehicle
Table 2 can have multiple rows per date & vehicle

I want to join on date and vehicle, and retrieve the min(table2.end\_times) that is greater than the table1.max\_job\_complete\_date. The desired value won't necessarily be the maximum end\_times from table2, so I can't simply use a row\_number() over partition by...

[simplified visual of the two tables](https://i.stack.imgur.com/QwfSj.png)

I tried the following, but this doesn't necessarily pull the max end\_time specifically for that date & driver.

```
select
a."date",
a.vehicle,
a.max_job_complete_date,
from table1 as a
left join table2 as b
on b."date" = a."date"
and b.vehicle = a.vehicle
where b.end_times >= (select(max_load_complete_time from table1))

```