SQL Joins : How can i get latest record from 2nd table (one to many relationship)

Posted on

Question :

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

Answer :

row_number()

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

Leave a Reply

Your email address will not be published. Required fields are marked *