Dmitry,

I haven't published this blog yet (it needs a bit of work), but take a look

----------------------------

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

And my timing results

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.