-1

I have two data tables. The first table called ClientDE has two primary keys - ClientID and LastPurchaseDate. The second table called LastPurchaseDE has one primary key - ClientID. The second table is populated with data from ClientDE and should have only the latest purchase. enter image description here

I use this SQL code:

SELECT a.clientId, a.LastPurchaseDate, a.PurchaseName
FROM 
(SELECT DISTINCT g.clientID, MAX(g.LastPurchaseDate), g.PurchaseName
FROM ClientDE g
GROUP BY g.clientID, g.PurchaseName) a
WHERE a.PurchaseName LIKE 'SHOP%'

ClientDE can have few records with the same ClientID and different date, I want to populate LastPurchaseDE only with the MAX date for the client. This SQL should retrieve unique values, but it doesn't

kacper99
  • 135
  • 1
  • 7
  • FYI: You don't need `DISTINCT` when you're using `GROUP BY`. The grouping already ensures that the results are distinct. – Barmar May 19 '22 at 20:17
  • Please be more specific than "doesn't work". Post some sample data and the desired result. – Barmar May 19 '22 at 20:19
  • You're getting the last date for each combination of client ID and purchase name, not the name of the last purchase for each client. See https://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group-mysql?rq=1 if the latter is what you want. – Barmar May 19 '22 at 20:20
  • It doesn't retrieve unique values – kacper99 May 19 '22 at 20:31
  • It can't retrieve duplicate values. They'll have different `PurchaseName`, so they're not duplicates. – Barmar May 19 '22 at 20:33
  • I want to retrieve only the latest date (MAX date), but I retrieve all dates, then I can't populate my second table. – kacper99 May 19 '22 at 20:34
  • You're retrieving the latest date for each purchase name, since you have `PurchaseName` in the `GROUP BY` clause. – Barmar May 19 '22 at 20:36
  • I suspect you just want `GROUP BY clientID`, not `GROUP BY clientID, purchaseName`. See the linked question for how to get the result you want. – Barmar May 19 '22 at 20:37

0 Answers0