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 : and the second table : 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

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

​x
 
Name    Count   Data1A   1   20190201B   3   20190201C   6   20190201​

and the second table :

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

 
Name    DateA   20190101B   20190203A   20190102B   20190103C   20190305C   20190809A   20190304B   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

 
Name    Count   Data1   Date2A   1   20190201    20190304B   3   20190201    20190203C   6   20190201    20190305​

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

Answer

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

 
select t1.name,t1.data1,t1.count,min(t2.date) as date2 from table1 t1 join table2 t2on t1.name=t2.nameand t1.date<t2.dategroup by t1.name,t1.date,t1.count​

Advertisement

Answer