SQL Server – Selecting Top 1 in a Left Join
This scenario doesn't come up too often, but the other day I had a requirement to do a left join through a one to many relationship but only get 1 result from the right side of the join. This kind of scenario can often be accomplished using sub selects, but in this case I needed access to multiple columns so a sub select would be out of the question.
The solution was to do an OUTER APPLY instead of a LEFT JOIN. An outer apply allows you to write a sub query referencing the ID from the original table just like a sub select. However as this is effectively a kind of join you have access to multiple columns.
Example Scenario
Imagine you have a table of customers and a table of addresses and each customer can have multiple addresses.
Our tables will look like this:
Customer (CustomerID, FirstName, LastName)
Address (AddressID, CustomerID, Line1, Line2, Town, County, Country, DateAdded)
The task is to get a list of customers and their most recent address.
Solution
By using an OUTER APPLY we can join the 2 tables and get the most recent address like this:
1SELECT c.*, la.*2FROM Customer c3OUTER APPLY4 (SELECT TOP 1 *5 FROM Address a6 WHERE a.CustomerID = c.CustomerID7 ORDER BY a.DateAdded DESC8 ) AS la