I was asked to get a list of customers with a single address for each, and preferably the Postal address if the customer has one.

Getting the first record is easy enough with a MIN function, but when you have to add an ORDER BY or GROUP BY the MIN function doesn’t quite work as you want it to. So this is what I came up with…

My setup for this example

Here are the tables I’m using in this example…

Option 1: Use ROW_NUMBER()

To do this I make my CustomerAddress table into a sub query and add an extra field with the ROW_NUMBER, then add a WHERE clause for where the row equals 1. Using ROW_NUMBER allows you to do ORDER BY.

Here’s how it looks…

SELECT c.ID, c.FirstName, ad.FullAddress
FROM dbo.Customer c
	LEFT OUTER JOIN (
		SELECT CustomerID, FullAddress
		FROM ( SELECT *
					, ROW_NUMBER() OVER(PARTITION BY CustomerID ORDER BY AddressType ASC) AS FirstAddressID
				FROM dbo.CustomerAddrss) tempAddress
		WHERE FirstAddressID = 1) ad ON c.ID = ad.CustomerID

This is my preferred option, to me it just seems more elegant. And as a bonus it is ever so slightly faster.

Option 2: Use FIRST_VALUE()

FIRST_VALUE() was introduced back in SQL Server 2012.

Like ROW_NUMBER, FIRST_VALUE is a Windows Function, which is an aggregate function and allows the use of partitioning and ordering over a rowset by using the OVER clause.

Here’s how it looks…

SELECT *
FROM dbo.Customer c
	LEFT OUTER JOIN (
		SELECT DISTINCT CustomerID
			, FIRST_VALUE(FullAddress) OVER (PARTITION BY CustomerID ORDER BY AddressType ASC) AS FirstAddress
		FROM dbo.CustomerAddrss
		) ca ON c.ID = ca.CustomerID

What if the sort is not straight forward?

In my example here, which is similar to my real life problem, I am able to order by the AddressType with certainty.

But what if there were other address types that meant I wasn’t after the first option alphabetically, i.e. if ‘Other’ was added as an AddressType. In such a case neither of the above would work as they currently stand.

If that is the case in your situation then you would need to customize the ORDER BY to something like…

ORDER BY CASE AddressType WHEN 'Postal' THEN '' ELSE AddressType END ASC