sql server 2005 - SQL Statement Help - Select latest Order for each Customer

Question

Say I have 2 tables: Customers and Orders. A Customer can have many Orders.

Now, I need to show any Customers with his latest Order. This means if a Customer has more than one Orders, show only the Order with the latest Entry Time.

This is how far I managed on my own:

SELECT a.*, b.Id
FROM Customer a INNER JOIN Order b ON b.CustomerID = a.Id
ORDER BY b.EntryTime DESC

This of course returns all Customers with one or more Orders, showing the latest Order first for each Customer, which is not what I wanted. My mind was stuck in a rut at this point, so I hope someone can point me in the right direction.

For some reason, I think I need to use the MAX syntax somewhere, but it just escapes me right now.

UPDATE: After going through a few answers here (there's a lot!), I realized I made a mistake: I meant any Customer with his latest record. That means if he does not have an Order, then I do not need to list him.

UPDATE2: Fixed my own SQL statement, which probably caused no end of confusion to others.

Answer 1

I don't think you do want to use MAX() as you don't want to group the OrderID. What you need is a ordered sub query with a SELECT TOP 1.

select * 
from Customers 
    inner join Orders 
        on Customers.CustomerID = Orders.CustomerID
        and OrderID = (
            SELECT TOP 1 subOrders.OrderID 
            FROM Orders subOrders 
            WHERE subOrders.CustomerID = Orders.CustomerID 
            ORDER BY subOrders.OrderDate DESC
        )