0

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

1 Answer 1

2

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.

2
  • Hi, looks like TOP 1 WITH TIES is producing a syntax error, at least in Query Studio. I'm partitioning by the ID, not email. Is that the problem?
    – nix9247
    Nov 24, 2020 at 16:04
  • I'm not a fan of Query Studio, tbh. I did fix a syntax issue in my examples, so please give it another try. The field you use in your partition shouldn't produce an error as long as it's in your source DE. Nov 24, 2020 at 18:16

This site is temporarily in read-only mode and not accepting new answers.

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