>USE AdventureWorks
> 
>-- This solution is SQL Server 2000 compatible - correlated subquery
>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
> 
>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.SalesOrderID =
>(SELECT TOP 1 SalesOrderID FROM  
>Sales.SalesOrderHeader OH WHERE OH.CustomerID = Cust.CustomerID ORDER BY OrderDate DESC)  
> 
> 
>
> 
> 
>-- 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)
> 
> 
>-- Compound key solution that outperforms all other solutions
>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 CompoundKey FROM  
>Sales.SalesOrderHeader OH GROUP BY CustomerID) LastOrder
>ON Cust.CustomerID = LastOrder.CustomerID and Ord.SalesOrderID  = CAST(RIGHT(LastOrder.CompoundKey,12) AS INT)
>SET STATISTICS TIME OFF

>19127 row(s) affected)
>
>(1 row(s) affected)
>
>SQL Server Execution Times:
>CPU time = 327 ms, elapsed time = 825 ms.
>
>(19119 row(s) affected)
>
>(1 row(s) affected)
>
>SQL Server Execution Times:
>CPU time = 578 ms, elapsed time = 1261 ms.
>
>(19127 row(s) affected)
>
>(1 row(s) affected)
>
>SQL Server Execution Times:
>CPU time = 265 ms, elapsed time = 810 ms.
>
>(19119 row(s) affected)
>
>(1 row(s) affected)
>
>SQL Server Execution Times:
>CPU time = 359 ms, elapsed time = 865 ms.
>
>(19119 row(s) affected)
>
>(1 row(s) affected)
>
>SQL Server Execution Times:
>CPU time = 436 ms, elapsed time = 972 ms.
>
>(19119 row(s) affected)
>
>(1 row(s) affected)
>
>SQL Server Execution Times:
>CPU time = 375 ms, elapsed time = 778 ms.