LEFT Join on a Subquery with specific criteria

I have two tables that I am trying to JOIN

table1
----------------------------
Id         Name         Num
123X       Apple        17

table2
-------------------------------------------------
id           EndDt         SomeVal      
123X         10/1/2021     xxx
123X         3/1/2022      yyy

I am attempting to Select from table1 a and LEFT JOIN table2 b on a.id = b.id – however, I want to only select on the id in table2 where MAX(EndDt)

Select a.*, b.SomeVal
from table1 a 
    LEFT OUTER JOIN table2 b on a.id=b.id // and b.MAX(EndDt)

Is something like that doable?

Answer

There are a few ways you can do this. I make some assumptions on your data though.

1. Use a LEFT JOIN with a subquery:

   SELECT T1.*,
          sq.SomeVal
   FROM dbo.Table1 T1
        LEFT JOIN (SELECT ROW_NUMBER() OVER (PARTITION BY t2.Id ORDER BY t2.EndDt DESC) AS RN,
                          t2.Id,
                          t2.SomeVal
                   FROM dbo.Table2 T2) sq ON T1.Id = T2.Id
                                         AND T2.RN = 1;
   

2. Use APPLY and TOP:

   SELECT T1.*,
          sq.SomeVal
   FROM dbo.Table1 T1
        OUTER APPLY (SELECT TOP (1)
                            t2.Id,
                            t2.SomeVal
                     FROM dbo.Table2 T2
                     WHERE T2.Id = T1.Id
                     ORDER BY T2.EndDt DESC) sq;
   

3. Use a CTE and get the “top 1” row per group:

   WITH CTE AS(
       SELECT T1.*,
              T2.SomeVal,
              ROW_NUMBER() OVER (PARTITION BY T1.ID ORDER BY T2.MaxDt DESC) AS RN
       FROM dbo.Table1 T1
            LEFT JOIN dbo.Table2 T2 ON T1.Id = T2.Id)
   SELECT *
   FROM CT
   WHERE RN = 1;
   

4. Use TOP (1) WITH TIES:

   SELECT TOP (1) WITH TIES
          T1.*,
          T2.SomeVal
   FROM dbo.Table1 T1
        LEFT JOIN dbo.Table2 T2 ON T1.Id = T2.Id
   ORDER BY ROW_NUMBER() OVER (PARTITION BY T1.ID ORDER BY T2.MaxDt DESC) ASC;
   

Note that options 3 and 4 won’t work as expected if ID is not unique in the table Table1 (hence my assumptions about your data).