Violation of primary key constraint

Question

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"

Answer 1

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:

SELECT a.clientId, a.LastPurchaseDate, a.PurchaseName
FROM 
(
 SELECT 
  g.clientID, 
  g.LastPurchaseDate, 
  g.PurchaseName, 
  ROW_NUMBER() OVER(PARTITION BY g.clientID ORDER BY g.LastPurchaseDate DESC) as rn
 FROM ClientDE g
 WHERE g.PurchaseName LIKE 'SHOP%'
) a
WHERE a.rn = 1

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.