Tag: Apply

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 c
3OUTER APPLY
4 (SELECT TOP 1 *
5 FROM Address a
6 WHERE a.CustomerID = c.CustomerID
7 ORDER BY a.DateAdded DESC
8 ) AS la