Join Tables Based on Most Recent Date

[Fletch12]

I have two tables, with sample data as below. I need to be able to find the stock value from Table1 for each item. But I need to find it for the supplier of the most recent receipt. I've highlighted the records from Table2 with the most recent dates, and shown the desired results I need at the bottom. What is the best approach for this?

Table1
Item,Supplier,Stock
ABC1,CompanyA,5
ABC1,CompanyB,3
ABC1,CompanyC,10
ABC2,CompanyA,50
ABC2,CompanyB,35

Table2
Item,Supplier,ReceivedDate
[highlight]ABC1,CompanyA,11/10/11[/highlight]
ABC1,CompanyA,11/08/11
ABC1,CompanyA,11/02/11
ABC1,CompanyB,10/15/11
ABC1,CompanyC,11/06/11
ABC1,CompanyC,11/07/11
ABC2,CompanyA,10/01/11
ABC2,CompanyB,10/05/11
[highlight]ABC2,CompanyB,10/22/11[/highlight]
ABC2,CompanyB,09/15/11

Desired Result
Item,Supplier,Stock
ABC1,CompanyA,5
ABC2,CompanyB,35

 

[markros]

select T.* from Table1 T
where Supplier = (select top (1) Supplier from Table2 T2
where T2.Item = T.Item ORDER BY ReceivedDate DESC)

This is one of many possible solutions.

To learn more, check

Including an Aggregated Column's Related Values
Including an Aggregated Column's Related Values - Part 2


PluralSight Learning Library