CODEWITHSUNDEEP
sqlms-access
CodeMed
CodeMed

Reputation: 9191

select each customer's most recent purchase

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?

Upvotes: 3

Views: 3840

Answers (2)

user20343656
user20343656

Reputation: 1

"AS" is not generally needed, but in a long piece of code that other people may need to look at later it can be REALLY helpful! As can choosing informative alias names.

Upvotes: -1

sgeddes
sgeddes

Reputation: 62831

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.

Upvotes: 3

Related Questions