how to pull latest record for each ID from 2 tables in Oracle SQL

Question

I have 2 tables named- Product and sold_product

sold_product

product_code    sold_date   product_id  product_rating
46077862546     18/08/21    380         3.5
41237862546     18/08/21    300         5.0
41237862789     06/08/21    356         4.0
            

Product

product_id  product_name    rack_no 
380         Television      5   
344         Refridgerator   4   
333         Air Conditioner 6   

Now I need to pull the latest sold_date, for every product_id with product_code and product_name. every product_id is having single(unique) entry in product table.

Answer 1

Try this:

select ap.prid,ap.prcode, a.name, ap.MaxDate from product a,(select prCode,prid, max(soldDate) as MaxDate 
from sold_product
group by prid) ap where a.id=ap.prid;

Answer 2

Try like this below:

select p.product_id, s.product_code, p.product_name, max(s.sold_date)
from product p
left join sold_product s on s.product_id = p.product_id
group by p.product_id, s.product_code, p.product_name