GROUP BY in Marketing Cloud always to get unique values

Question

We have a data extension to which the data is being imported from another system. Sometimes, an Id that's used as SubscriberKey for sending emails appears twice (we are using another primaryKey in SFMC DE which is generated uniquely by that system for each row so there's no PK violation). Now, we wanted to use a GROUP BY function, but although the syntax is correct, the results are not grouped by this ID and therefore, we're still getting duplicates. I tried removing the primaryKey, but it did not help. Here's the code:

SELECT 
COUNT(Consent_Name) AS Instances,
ID,
Product,
Customer_Name,
Email
FROM table_1
GROUP BY 
ID,Product,Customer_Name,Email

Answer 1

The easiest thing, in my opinion, is to use the T-SQL windowing functions to pick one of the duplicates with sort criteria. Simply set the partition by to match the primary key of your target DE.

SELECT top 1 with ties
, ID
, Product
, Customer_Name
, Email
FROM table_1
order by row_number() over (partition by ID order by newid())

The top 1 with ties selects the first row by ID. What's first is determined by the order by newid(). The newid() is just a random GUID.

If you had other criteria like updatedDate in each DE row, you could pick the most recent like this

SELECT top 1 with ties
, ID
, Product
, Customer_Name
, Email
FROM table_1
order by row_number() over (partition by ID order by updatedDate desc)

There's also a long-form of the windowing function method in the link I mentioned earlier. If you want to validate the results of the partition, I'd suggest using the long-form version until you're comfortable with how it works.