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

Something like this should do it:

SELECT X.*, Y.LatestOrderId
FROM Customer X
LEFT JOIN (
  SELECT A.Customer, MAX(A.OrderID) LatestOrderId
  FROM Order A
  JOIN (
    SELECT Customer, MAX(EntryTime) MaxEntryTime FROM Order GROUP BY Customer
  ) B ON A.Customer = B.Customer AND A.EntryTime = B.MaxEntryTime
  GROUP BY Customer
) Y ON X.Customer = Y.Customer

This assumes that two orders for the same customer may have the same EntryTime, which is why MAX(OrderID) is used in subquery Y to ensure that it only occurs once per customer. The LEFT JOIN is used because you stated you wanted to show all customers - if they haven't got any orders, then the LatestOrderId will be NULL.

Hope this helps!

--

UPDATE :-) This shows only customers with orders:

SELECT A.Customer, MAX(A.OrderID) LatestOrderId
FROM Order A
JOIN (
  SELECT Customer, MAX(EntryTime) MaxEntryTime FROM Order GROUP BY Customer
) B ON A.Customer = B.Customer AND A.EntryTime = B.MaxEntryTime
GROUP BY Customer