SQL Joins : How can i get latest record from 2nd table (one to many relationship) 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 &lt; d2.date OR d.date = d2.date AND d.prod_code &lt; d2.prod_code))
WHERE d2.prod_code IS NULL;


but the above is giving me one recent row of the last product added
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