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"