Most recent record in a left join

Question

Imagine I have the following 3 tables in SqlServer:

Customer (CustomerID, FirstName, LastName) Address (AddressID, CustomerID, Line1, City, State) Product (ProductID, CustomerID, Description) 

A customer can have multiple delivery addresses and mulitple products.

What I would like to do is to list the number of customers for each State where the State is determined by the most recent Address record. Such as "How many customers last received a product in each State?". Therefore I'm not interested in any previous Address records for the Customer, only the most Recent (determined by AddressID).

State | Number of Customers -------------------------- CA    | 32 GA    | 12 TX    | 0 OH    | 18 

I would normally do something like:

SELECT a.State, count(c.CustomerID) FROM Product p INNER JOIN Customer c ON c.CustomerID = p.CustomerID LEFT JOIN Address a ON a.CustomerID = c.CustomerID WHERE p.ProductID = 101 GROUP BY a.State 

However, as a Customer may have multiple Addresses will the customer only be counted in the State of the most recent Address record?

P.S. The above is purely an example scenario to easily explain the joins I am trying to achieve and does not reflect an actual system design.

Answer 1

Try this:

SELECT a.State, count(c.CustomerID) FROM Product p INNER JOIN Customer c ON c.CustomerID = p.CustomerID LEFT JOIN Address a ON a.CustomerID = c.CustomerID        AND a.AddressID =          (            SELECT MAX(AddressID)             FROM Address z             WHERE z.CustomerID = a.CustomerID         ) WHERE p.ProductID = 101 GROUP BY a.State 

Answer 2

You could also try (assuming I remember my SQLServer syntax correctly):

SELECT state, count(customer_id) FROM (     SELECT         p.customer_id         , (SELECT TOP 1 State FROM Address WHERE Address.CustomerID = p.CustomerID ORDER BY Address.ID DESC) state     FROM Product p     WHERE p.ProductID = 101) GROUP BY state