trying to get a 1to1 instead of a 1tomany join 2

[peterlum]

im trying to get a 1 to many join to be a 1 to 1 on a filtered criteria. So to simplify I have 2 tables

Accounts | [acctnum], [name]
Purchases | [acctnum], [ammount], [purchasedate]

There can be many purchases to one account but I just want joins from its single most recent purchasedate. I've looked into using top 1 but i havent been able to get the sytanx for that to work.

Any help with this would great, thanks.

 

[r937]

SELECT Accounts.acctnum
     , Accounts.name
     , Purchases.ammount
     , Purchases.purchasedate
  FROM Accounts
INNER
  JOIN ( SELECT acctnum
              , MAX(purchasedate) AS last_purchase
           FROM Purchases
         GROUP
             BY acctnum ) AS m
    ON m.acctnum = Accounts.acctnum
INNER
  JOIN Purchases
    ON Purchases.acctnum = Accounts.acctnum
   AND Purchases.purchasedate = m.last_purchase

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[markros]

We're preparing a blog on this very topic. Stay tuned.

 

[peterlum]

thanks r937 that works almost perfectly but i have certain instances where there are multiple purchases on the same day, how would i go about grabbing just one, say the highest valued purchase. the reason i need this is because an account can only appear once in my report even if there are 5 purchases on the same date. thanks again

 

[r937]

if there are any additional columns in the Purchases table (which you did not mention, but which would be included in the SELECT clause), then you gots to do it like this --

SELECT Accounts.acctnum
     , Accounts.name
     , Purchases.ammount
     , Purchases.purchasedate
  FROM Accounts
INNER
  JOIN ( SELECT acctnum
              , MAX(purchasedate) AS last_purchase
           FROM Purchases
         GROUP
             BY acctnum ) AS m    
    ON m.acctnum = Accounts.acctnum
INNER
  JOIN ( SELECT acctnum
              , purchasedate
              , MAX(ammount) AS highest_amount
           FROM Purchases
         GROUP
             BY acctnum
              , purchasedate ) AS m2   
    ON m2.acctnum = Accounts.acctnum
   AND m2.purchasedate = m.last_purchase 
INNER
  JOIN Purchases
    ON Purchases.acctnum = Accounts.acctnum
   AND Purchases.purchasedate = m.last_purchase 
   AND Purchases.ammount = m2.highest_amount

however, if those really are the only columns in your table, you can get away with this --

SELECT Accounts.acctnum
     , Accounts.name
     , MAX(Purchases.ammount) AS ammount
     , Purchases.purchasedate
  FROM Accounts
INNER
  JOIN ( SELECT acctnum
              , MAX(purchasedate) AS last_purchase
           FROM Purchases
         GROUP
             BY acctnum ) AS m    
    ON m.acctnum = Accounts.acctnum
INNER
  JOIN Purchases
    ON Purchases.acctnum = Accounts.acctnum
   AND Purchases.purchasedate = m.last_purchase 
GROUP
    BY Accounts.acctnum
     , Accounts.name
     , Purchases.purchasedate

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[r937]

and of course your next post will say "what if there are two purchases of the same amount on the same day" along with other columns...

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[markros]

What SQL Server version you're using? In SQL Server 2005 and up just use new windowing functions, e.g.

select * from (select * , row_number() over (partition by AccountNumber order by OrderDate DESC, SubTotal Desc) as rownumber from myTable) MT where RowNumber = 1

 

[markros]

More on this problem

Below are 5 different solutions to this common problem.

USE AdventureWorks
 
-- This solution is SQL Server 2000 compatible
SET STATISTICS TIME ON
SELECT Cust.[CustomerID],
      Cust.[TerritoryID],
      Cust.[AccountNumber],
      Cust.[CustomerType],
      Ord.[SalesOrderID],      
      Ord.[OrderDate],
      Ord.[DueDate],
      Ord.[ShipDate],
      Ord.[Status],      
      Ord.[SubTotal], Ord.[TaxAmt],
      Ord.[Freight],  Ord.[TotalDue]  FROM Sales.Customer Cust
INNER JOIN Sales.SalesOrderHeader Ord
ON Cust.CustomerID = Ord.CustomerID
WHERE Ord.OrderDate =
(SELECT MAX(OrderDate) AS LastDate FROM  
Sales.SalesOrderHeader OH WHERE OH.CustomerID = Ord.CustomerID)
 
-- The above solution will return duplicate records if there is more than 1 maximum date for a given customer
 
 
-- This is SQL Server 2000 compatible solution based on derived table idea
SELECT Cust.[CustomerID],
      Cust.[TerritoryID],
      Cust.[AccountNumber],
      Cust.[CustomerType],
      Ord.[SalesOrderID],      
      Ord.[OrderDate],
      Ord.[DueDate],
      Ord.[ShipDate],
      Ord.[Status],      
      Ord.[SubTotal], Ord.[TaxAmt],
      Ord.[Freight],  Ord.[TotalDue] FROM Sales.Customer Cust
INNER JOIN Sales.SalesOrderHeader Ord
ON Cust.CustomerID = Ord.CustomerID
INNER join (SELECT CustomerID, MAX(OrderDate) AS LastDate FROM  
Sales.SalesOrderHeader OH GROUP BY CustomerID) LastOrder
ON Cust.CustomerID = LastOrder.CustomerID and Ord.OrderDate = LastOrder.LastDate
 
-- The same comment as above applies - it will return duplicate records in case of several same last dates for the Customer
 
 
--- Two solutions bellow are only available in SQL Server 2005 and up - if we want them to return multiple records
--- We would need to use RANK() function instead of ROW_NUMBER()
 
SELECT * FROM (SELECT Cust.[CustomerID],
      Cust.[TerritoryID],
      Cust.[AccountNumber],
      Cust.[CustomerType],
      Ord.[SalesOrderID],      
      Ord.[OrderDate],
      Ord.[DueDate],
      Ord.[ShipDate],
      Ord.[Status],      
      Ord.[SubTotal], Ord.[TaxAmt],
      Ord.[Freight],  Ord.[TotalDue],
      ROW_NUMBER() OVER (PARTITION BY Ord.CustomerID
      ORDER BY OrderDate DESC) AS rown  FROM Sales.Customer Cust
INNER JOIN Sales.SalesOrderHeader Ord
ON Cust.CustomerID = Ord.CustomerID) Ordered WHERE rown = 1
 
SELECT TOP 1 WITH ties  Cust.[CustomerID],
      Cust.[TerritoryID],
      Cust.[AccountNumber],
      Cust.[CustomerType],
      Ord.[SalesOrderID],      
      Ord.[OrderDate],
      Ord.[DueDate],
      Ord.[ShipDate],
      Ord.[Status],      
      Ord.[SubTotal], Ord.[TaxAmt],
      Ord.[Freight],  Ord.[TotalDue] FROM Sales.Customer Cust
INNER JOIN Sales.SalesOrderHeader Ord
ON Cust.CustomerID = Ord.CustomerID
ORDER BY ROW_NUMBER() OVER (PARTITION BY Ord.CustomerID
      ORDER BY OrderDate DESC)
 
 
-- Finally, this is very interesting solution based on compound key idea, which seems to outperform all solutions above
-- I first learned this idea from this FAQ (in Russian) <a href="[URL unfurl="true"]http://forum.foxclub.ru/read.php?32,177183,177232#msg-177232">http://forum.foxclub.ru/read.php?32,177183,177232#msg-177232</a>[/URL]
SELECT Cust.[CustomerID],
      Cust.[TerritoryID],
      Cust.[AccountNumber],
      Cust.[CustomerType],
      Ord.[SalesOrderID],      
      Ord.[OrderDate],
      Ord.[DueDate],
      Ord.[ShipDate],
      Ord.[Status],      
      Ord.[SubTotal], Ord.[TaxAmt],
      Ord.[Freight],  Ord.[TotalDue] FROM Sales.Customer Cust
INNER JOIN Sales.SalesOrderHeader Ord
ON Cust.CustomerID = Ord.CustomerID
INNER join (SELECT CustomerID,
MAX(CONVERT(NVARCHAR(30), OrderDate, 126) + CAST(SalesOrderID AS CHAR(12))) AS MaxID FROM  
Sales.SalesOrderHeader OH GROUP BY CustomerID) LastOrder
ON Cust.CustomerID = LastOrder.CustomerID and Ord.SalesOrderID  = CAST(RIGHT(LastOrder.MaxID,12) AS INT)
SET STATISTICS TIME OFF

 

[markros]

Please see the following blog

http://blogs.lessthandot.com/admin.php?ctrl=items&blog=7&p=528

for detailed analysis of the above problem.

 

[markros]

Oops, wrong link

http://blogs.lessthandot.com/index....rver/including-an-aggregated-column-s-related