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:

SELECT c.*, la.*
FROM Customer c
OUTER APPLY 
    (SELECT TOP 1 *
    FROM Address a
    WHERE a.CustomerID = c.CustomerID
    ORDER BY a.DateAdded DESC
    ) AS la

Advertisements

3 thoughts on “SQL Server – Selecting Top 1 in a Left Join

  1. Connie McBride says:

    If you have 9 k customers, and 25 k addresses, and you write it like this:
    SELECT c.*, la.*
    FROM Customer c
    OUTER APPLY
    (SELECT TOP 1 *
    FROM Address a
    WHERE a.ID = c.AddressID
    ORDER BY a.DateAdded DESC
    ) AS la
    is there any benefit vs using a left outer join (since it will only link to one record?

    • timgriff84 says:

      No you should use a left join, an outer apply would be significantly worse.

      You would only use the outer apply in a one to many relation and you want to limit the results to 1 row from the many side.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s