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

Question

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 :)

score 2 · Accepted Answer · answered Oct 15 '19 at 08:56

2

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

answered Oct 15 '19 at 08:56

Zaynul Abadin Tuhin

31,407
5
33
63

score 2 · Answer 2 · answered Oct 15 '19 at 09:09

You want the earliest date from the second table that is also greater than the date in the first table:

There are multiple solutions including a correlated sub-query:

SELECT t1.*,
       ( SELECT MIN( "DATE" )
         FROM   Table2 t2
         WHERE  t1.name  = t2.name
         AND    t1.data1 < t2."DATE" ) AS data2
FROM   table1 t1

or

SELECT name,
       "COUNT",
       data1,
       data2
FROM   (
  SELECT t1.*,
         t2."DATE" AS data2,
         ROW_NUMBER() OVER ( PARTITION BY t1.name ORDER BY t2."DATE" ) AS rn
  FROM   table1 t1
         INNER JOIN table2 t2
         ON ( t1.name = t2.name AND t1.data1 < t2."DATE" )
)
WHERE  rn = 1

These are probably the better solutions than the others because they do not require aggregation over all the data. There is one other method that is probably faster on large data, but the OP has already accepted an answer. — Gordon Linoff, Oct 15 '19 at 11:33

Popeye · Answer 3 · 2019-10-15T09:10:27.653

1

You need analytical function:

SELECT NAME, COUNT_, DATA1, DATE2 FROM
(SELECT T1.NAME, T1.COUNT_, T1.DATA1, 
        ROW_NUMBER() 
        OVER (PARTITION BY T1.NAME ORDER BY T2.DATE) AS RN, 
        T2.DATE AS DATE2
FROM TABLE1 T1 JOIN TABLE2 T2
ON (T1.NAME = T2.NAME))
WHERE T2.DATE > T1.DATA1
WHERE RN = 1

Cheers!!

edited Oct 15 '19 at 09:10

answered Oct 15 '19 at 08:57

Popeye

35,427
4
10
31

score 1 · Answer 4 · answered Oct 15 '19 at 08:58

1

You can simply use aggregation:

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

answered Oct 15 '19 at 08:58

GMB

216,147
25
84
135

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

4 Answers4