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

enter image description here

I use this SQL code:

SELECT a.clientId, a.LastPurchaseDate, a.PurchaseName
(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"

  • 1
    Please update your question to include the details about the primary key in the target data extension. May 19, 2022 at 21:22

1 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
  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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .