Innovative use of the SQL Server row_number() function

I came across an interesting use of the row_number function today. Say you have a situation where you want to select the first address for an account that may have multiple addresses. You can use the row_number() function to create a query table and then select where row number = 1.

Previously you would have had to create a temporary table in a stored proc to achieve this. Very neat being able to do it this way!

SELECT *

FROM Account a

LEFT JOIN (SELECT *, row_number() OVER(partition BY AccountID

ORDER BY CASE AddressType WHEN 'main' THEN 0 WHEN 'mailing' THEN 1 ELSE 2 end) AS 'RowNumber' FROM AccountAddress ) AS addr

ON a.accountid = addr.accountid AND addr.rownumber = 1