How can I select unique records based on date?

[Erik Pygott]

I have a table tracking purchases by customers and want to build a query that pulls each customer's last purchase. To simplify the example, let's say the table is called Customers and has ID, Name, and Purchase_Date. I'd like to get every ID and Name and only the last purchase.

I tried the query below but it only pulls records with the latest date, not the latest date of every record.

SELECT ID, Name, Purchase_Date
FROM Customers
WHERE Purchase_Date = (SELECT max(Purchase_Date) from Customers);

Any thoughts?

[beacon]

Hi Erik,

Is the ID unique to each Customer? If so, then the following query should provide you with the last purchase date for each customer:

SELECT Customers.ID, Customers.CustomerName, Max(Customers.PurchaseDate) AS MaxOfPurchaseDate
FROM Customers
GROUP BY Customers.ID, Customers.CustomerName;
 

If the ID is for the order/purchase and you have a separate unique ID for the customers, you'll need a query that just pulls the customer's ID with the MAX of the purchase date, then join this query with another query that includes the max purchase date from the first query and the customer's name from the table where you're storing the customer's name.

Hope this helps and good luck,
beacon