I have 2 tables and for every id in the first table I need to find max value in the date_2 column that would be lower than a value in the date_1 column.

Tables:

table 1

table 2

outcome needed:

appreciate your help with this!

CodePudding user response：

you could rank each row (I'm doing it here with row_number() function) then match on the id and the ranking.

with t1 as (select id, date_1,
              row_number() over (partion by id order by date1) as rn
         from table1),
     t2 as (select id, date_2,
            row_number() over (partion by id order by date2) as rn
     from table2 ),
     select id, date1, date2
     from t1 inner join t2 on t1.id = t2.id and t1.rn = t2.rn

CodePudding user response：

You can pretty much write a simple correlated query using exists that mirrors the English narrative:

select id, (   
  select Max(date_2)          /* find max value in the date_2 column */
  from t2 
  where t2.id = t1.id         /* for every id in the first table */
    and t2.date_2 < t1.date_1 /* lower than a value in the date_1 column */
) as "max(date_2) within id,date_1"
from t1;