Pull most recent rows per customer 1

[Glasgow]

Suppose I have an Orders table with 3 fields: CustomerId, OrderDate and OrderAmount. There can be several orders per customer.

How would I write a SELECT query that just returned the most recent row (i.e. highest OrderDate value) for each customer? So, if there were 100 customers with 3 orders per customer (300 rows in all), the query would return the 100 most recent orders?

I'm sure it should be easy but I just can't get there!

Thanks in advance.

 

[RiverGuy]

SELECT a.*
FROM Orders a
INNER JOIN 
  (SELECT CustomerID, MAX(OrderDate) AS MaxDate FROM Orders b GROUP BY CustomerID) c
ON a.CustomerID = c.CustomerID AND a.OrderDate = c.MaxDate

 

[Glasgow]

Yep that does it, thank you.

 

[tran008]

Using sql2005....

SELECT a.* FROM (
SELECT customerID, OrderDate, ROW_NUMBER() OVER (PARTITION BY customerID ORDER BY OrderDate DESC) AS RecID FROM Orders
) AS a WHERE RecID = 1