sql - Oracle - Getting the latest list price from list table and append it another product table -

i have list price table, product prices keeps on changing , table product table , has entry of dates when product sold , @ price. want compare price of product on or before date ( when sold) , put list price in product table along selling price.

table :

list price , valid ,valid product   23  , jul 7 , july 15, x    24 , jul 20 , july 30,x    25 , aug 5 , aug 30,x    20,sep 5,sep 26,x 

product table :

product , selling price , of date   x , 24 , jul 10   x,39, jul 30   x,40, aug 28 

i wish append column(listprice) product table using closest dates as_of_date column , list price on date in price table.

select product.*, price.list_price  (select '2017-07-20 12:00:00'::timestamp  valid_from,select '2017-07-20 12:00:00'::timestamp valid_from) price   left join (select '2017-07-20 12:15:00'::timestamp ts, true val  )product on product.ts >= price.valid_from , b.ts < price.valid_to;   

please suggest way to in oracle.

just use join

select p.*, lp.* products p left join      listprices lp      on p.product = lp.product ,         p.asofdate >= lp.validfrom ,         p.asofdate <= lp.validto; 

if don't want take validto dates account . . .

select p.*,        (select max(lp.price) keep (dense_rank first order lp.validfrom desc)         listprices lp         p.product = lp.product ,               p.asofdate >= lp.validfrom       ) listprice products p ;