How to link WITH sub-query into main query

Other SQL Server Topics
1

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
(SELECT
ADDRESSID,
ENTITYID, -- this field links the Account table via the AccountID
ADDRESS,
DESCRIPTION,
ROW_NUMBER() OVER (PARTITION BY ENTITYID, DESCRIPTION ORDER BY AddressID DESC ) AS rn
FROM sysdba.ADDRESS AS m)
SELECT
LastAddress.,
sysdba.account.
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.

Any ideas?

2

Assuming that your AddressID is such that the larger it is, the more recent it is, I'd do something like:

Drop Table If Exists #Address
Create Table #Address(AddressID Int Identity(1,1), EntityID Int, Description VarChar(200), Address VarChar(200))
Drop Table If Exists #Account
Create Table #Account(AccountID Int, AccountName VarChar(100))

Insert Into #Account(AccountID,AccountName)
Values(1,'Bob'),(2,'Fred'),(3,'John')

Insert Into #Address(EntityID, Description,Address)
Values(1,'Shipping','1 Fake St'),(1,'Billing','1 Fake St'),
(2,'Shipping','345 Old Avenue'),(2,'Billing','345 Old Road'),(2,'Shipping','1 New St'),(2,'Shipping','This one'),
(3,'Shipping','345 Old Avenue'),(3,'Billing','345 Old Road'),(3,'Billing','1 New Rd')

;With LatestAddress As
(Select EntityID, Description, Max(AddressID) As LatestAddressID From #Address Group By EntityID,Description)
Select 
	ACC.AccountID,
	ADR.Address
From #Account ACC
Join #Address ADR On ACC.AccountID = ADR.EntityID 
Join LatestAddress LA On 
	LA.EntityID = ADR.EntityID
	And LA.LatestAddressID = ADR.AddressID
Where LA.Description = 'Shipping'

Which also avoids using Row_Number() in your CTE, which can lead to performance issues (since the CTE code can end up running multiple times)

3

Or even:

Select Distinct 
	EntityID, 
	Last_Value(Address) Over (Partition By EntityID,Description Order By AddressID Range Between Unbounded Preceding And Unbounded Following) As LastAddressID
From #Address
Where Description = 'Shipping'

Which might be cheaper

4

Or:

Select Distinct 
	EntityID, 
	First_Value(Address) Over (Partition By EntityID,Description Order By AddressID Desc) As LastAddressID
From #Address
Where Description = 'Shipping'

Which I think is possibly a tad better since it avoids needing a full range

5

Thanks Andy.

I don't have the function for First_Value() or Last_Value(), also I should add that the ID, are not numeric, but instead, they're like this:

a6UJ9A0023J1
a6UJ9A0023J2
a6UJ9A0023J3
a6UJ9A0023J4
a6UJ9A0023WX
a6UJ9A0023WY
a6UJ9A0023X0
a6UJ9A004LWB
a6UJ9A004LWC

Does that effect the MAX() function (Sorry, I should have mentioned that earlier). These IDs do seem to ascend.

Also, I assume #Address refers to the table, and not the field?

6

Get the Description out of the PARTITION because it could add huge overhead and you don't need it.

SELECT
LastAddress.col,
AC.col
FROM sysdba.ACOUNT AC
LEFT OUTER JOIN (
    SELECT *, ROW_NUMBER() OVER ( PARTITION BY ENTITYID ORDER BY AddressID DESC ) AS rownum
    FROM sysdba.ADDRESS
    WHERE Description = 'Shipping'
) AS LastAddress ON (LastAddress.rownum = 1 AND LastAddress.EntityID = AC.AccountID)
1 Like