I have two tables

1.Product table having columns

  • prod_code
  • alert_limit
  • prod_description

2.Daily_data table having columns

  • prod_code
  • used_today
  • date
  • stock_left

How should i join these two tables to get recent records of each product on the basis of date ? I have tried

SELECT p.*, d.*
FROM product p
JOIN daily_data d ON (p.product_code = d.prod_code)
LEFT OUTER JOIN daily_data d2 ON (p.product_code = d.prod_code AND 
(d.date < d2.date OR d.date = d2.date AND d.prod_code < d2.prod_code))
WHERE d2.prod_code IS NULL;

but the above is giving me one recent row of the last product added


1 Answer 1



select * from 
select *
     , row_number() over (partition by table2.prod_code order by table2.date desc) as rn
from table1 
join table2 
  on table1.prod_code = table2.prod_code 
) tt 
where rn = 1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.