I'm trying to link two tables (Account and Address), but only wanting to get the most recent Shipping Address of each Account (the Address table holds the history of addresses, both for shipping and billing etc). I'm trying to use this sub-query to get the most recent Shipping address, but cannot seem to link the Address from the sub-query in to a main query with the rest of the Account data.
WITH LastAddresses AS
ENTITYID, -- this field links the Account table via the AccountID
ROW_NUMBER() OVER (PARTITION BY ENTITYID, DESCRIPTION ORDER BY AddressID DESC ) AS rn
FROM sysdba.ADDRESS AS m)
FROM LastAddresses JOIN sysdba.ACOUNT on (LastAddress.EntityID = sysdba.ACOUNT.AccountID)
WHERE rn = 1 AND LastAddress.Description = 'Shipping'
This SQL works fine with references/links to the Account table removed, but not at all when I try to join these tables. I'm using MS SQL Server 2008.