I have two tables that I am trying to JOIN
x
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?
Advertisement
Answer
There are a few ways you can do this. I make some assumptions on your data though.
- 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;
- Use
APPLY
andTOP
: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;
- 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;
- 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).