select each customer's most recent purchase

Question

In Microsoft Access 2010, I want to run a query that lists only the most recent purchase made by each and every customer. Here is the SQL that I have so far:

SELECT ClientNumber, DateOfPurchase  
FROM ordersTable WHERE ClientNumber IN (  
SELECT MAX(DateOfPurchase)  
FROM ordersTable  
GROUP BY ClientNumber  
);  

The problem is that this query is not returning any data, despite their being relevant data in ordersTable. How do I alter the code above so that it works?

Answer 1

Typically you can solve this by joining the table to itself:

SELECT o.ClientNumber, o.DateOfPurchase  
FROM ordersTable o JOIN (
    SELECT MAX(DateOfPurchase) as MaxDateOfPurchase, ClientNumber
    FROM ordersTable  
    GROUP BY ClientNumber  
) t ON o.ClientNumber = t.ClientNumber AND o.DateOfPurchase = t.MaxDateOfPurchase

With that said, if you're not selecting any other fields, the subquery will work by itself:

SELECT MAX(DateOfPurchase) as MaxDateOfPurchase, ClientNumber
FROM ordersTable  
GROUP BY ClientNumber  

Edit, given you're using MS Access, you will perhaps need the keyword AS when aliasing a column.