TOP n records per group You want the three most recent orders for each client. Use a subquery to select the 3 top orders per client, and use it to limit which orders are selected in the main query:
SELECT Orders.CustomerID, Orders.OrderDate, Orders.OrderID
FROM Orders
WHERE Orders.OrderID IN
(SELECT TOP 3 OrderID
FROM Orders AS Dupe
WHERE Dupe.CustomerID = Orders.CustomerID
ORDER BY Dupe.OrderDate DESC, Dupe.OrderID DESC)
ORDER BY Orders.CustomerID, Orders.OrderDate, Orders.OrderID;
Points to note:- Since we have two copies of the same table, we need the alias.
- Like EXISTS in the first example above, there is no problem with the subquery returning multiple records. The main query does not have to show any value from the subquery.
- Adding the primary key field to the ORDER BY clause differentiates between tied values.