Select that joins two tables Oracle PL/SQL

Question

I've got two tables wchich I need to join with Select and I've got a problem. The tables look like that: table_price

Product_ID | Buy_date | Buy_price |
     1     | 16.10.01 |  2.50     |
     1     | 16.11.02 |  3.20     |
     2     | 16.10.31 |  3.80     |

table expire_date

Product_ID | Count  | Exp_date |
     1     |  1000  |  17.10.01|
     1     |  500   |  17.11.31|
     2     |  500   |  17.11.01|

I need to write a select in Oracle PL/SQL wchich gives me following results:

Product_ID| Count | Exp_date| last_buy_price|
     1    | 1000  | 17.10.01| 3.20          |
     1    | 500   | 17.31.31| 3.20          |
     2    | 500   | 17.11.01| 3.80          |

It means that it will give me every expire date with count of product from table expire_date and match it with last buy price from table_price with product_id (always with last buy price, ordered by column buy_date) Please guys help me, I've tried so many codes and I still can't get satysfying results

Answer 1

A correlated subquery using keep is possibly the most performant method:

select ed.*,
       (select max(p.buy_price) keep (dense_rank first order by p.buy_date desc)
        from table_price p
        where p.product_id = ed.product_id
       ) as last_buy_price
from expire_date ed;

You could, of course, also express this in the from clause:

select ed.*, p.last_buy_price
from expire_date ed left join
     (select p.product_id,
             max(p.buy_price) keep (dense_rank first order by p.buy_date desc) as last_buy_price
      from table_price p
     ) p
     on p.product_id = ed.product_id;

Answer 2

You can use ROW_NUMBER() :

SELECT ed.*,
       tp.buy_price as last_buy_price
FROM expire_date ed
JOIN(SELECT s.*,
            ROW_NUMBER() OVER(PARTITION BY s.product_id ORDER BY s.buy_date DESC) as rnk
     FROM table_price s) tp
 ON(ed.product_id = tp.product_id and tp.rnk = 1 )