SQL Oracle closest match variable-CodePudding

I have below two tables

~ What I am looking to do: I want to append the Price from Table 2 to Table 1. If the exact Quantity in Table 1 is not there in Table 2, take the closest max Quantity from Table 2 [i.e. 12 Quantity is not there in Table 2, the closest max is 20, so I want the price of that]

So the result should look like:

I have tried below query but this does not give the correct output. I just want 1 price for each quantity as indicated in Table 1.

select distinct
t1.Product_NR,
t1.Customer,
t1.Quantity,
t2.price

from Table_1 t1
join Table_2 t2 on t1.Product_NR = t2.Product_NR
                and t1.Quantity <= t2.Quantity

CodePudding user response：

Here is one option:

select product_nr,customer,quantity,price from (
select 
t1.product_nr,t1.customer,t1.quantity,t2.quantity 
a,t2.price,min(t2.quantity)over(partition by t1.product_nr,t1.customer) b
from table1 t1 join table2 t2 on t1.product_nr=t2.product_nr and 
t2.quantity>=t1.quantity)
where a=b

sample data:

create table table1 (product_nr number,customer varchar2(2), quantity number);
create table table2 (product_nr number, quantity number, price number);

insert into table1 values (10023,'x',12);
insert into table1 values (10023,'y',10);
insert into table1 values (10334,'x',1);


insert into table2 values (10023,1,100);
insert into table2 values (10023,10,120);
insert into table2 values (10023,20,250);
insert into table2 values (10023,100,400);
insert into table2 values (10334,0,25);
insert into table2 values (10334,200,600);

query output:

10023   x   12  250
10023   y   10  120
10334   x   1   600

db<>fiddle