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