Skip to content
Advertisement

LEFT Join on a Subquery with specific criteria

I have two tables that I am trying to JOIN

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)

Is something like that doable?

Advertisement

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:
  2. Use APPLY and TOP:
  3. Use a CTE and get the “top 1” row per group:
  4. Use TOP (1) WITH TIES:

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).

User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement