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?

CodePudding user response：

I would recommend using the windowed ROW_NUMBER function to take the max table2 first, and then join into that subquery.

;WITH cte AS (
    SELECT *, [Row] = ROW_NUMBER() OVER (PARTITION BY b.Id ORDER BY b.EndDt DESC)
    FROM table2 b
)
SELECT a.*, cte.SomeVal
FROM table1 a
LEFT JOIN cte ON a.id = cte.id AND cte.[Row] = 1

CodePudding user response：

For a single value, use a correlated sub-query:

SELECT
    a.id,
    a.name,
    a.num,
    (
        SELECT TOP 1 SomeValue 
        FROM table2 As b 
        WHERE b.id = a.id 
        ORDER BY b.EndDt DESC
    ) As SomeVal
FROM
    table1 a

CodePudding user response：

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 after and the 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;