Search code examples
sqloraclegreatest-n-per-group

First date from second table where date is bigger than date in table first oracle

I want pull out the date from second table. My second table may have a over 100 dates to a unique record(A,B,C). I have I first Table :

Name    Count   Data1
A   1   20190201
B   3   20190201
C   6   20190201

and the second table :

Name    Date
A   20190101
B   20190203
A   20190102
B   20190103
C   20190305
C   20190809
A   20190304
B   20190405

I want to first table pull out the date first date from second table when date is bigger than the date1 from first table.

The result must be :

Name    Count   Data1   Date2
A   1   20190201    20190304
B   3   20190201    20190203
C   6   20190201    20190305

How to extract the date. It must be a procedure or another solution. Thanks for help :)

Solution

  • use join and min()

    select t1.name,t1.data1,t1.count,min(t2.date) as date2
 from table1 t1 join table2 t2
on t1.name=t2.name
and t1.date<t2.date
group by t1.name,t1.date,t1.count