I have two data extensions. The first data extension called ClientDE has two primary keys – ClientID and LastPurchaseDate. The second data extension called LastPurchaseDE has one primary key – ClientID. The second data extension is populated with data from ClientDE and should have only the latest purchase
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 onl with the MAX date for the client. When I run this query above I get error "Violation of primary key constraint"
Best Answer
Assuming you want just ClientID as the primary key and the most recent date only, I would recommend going with a ROW_NUMBER() and Partition instead of GROUP BY and DISTINCT.
Something like below should get you there:
I did not run this myself to validate, so you may need to play with it some to get it 100% functional - but it should at least get you mostly there.