How does Marketing Cloud's SQL in Automation Studio works when having a DISTINCT operator on a data extensions that has a Primary Key (in my case "Customer_Key")? Specifically, does it look for unique primary key values, and ignores the duplicate primary keys form the end-result, or is it looking at the whole data extension for duplicates (i.e. duplicate rows)? If the answer is the latter, how then I can make MC's DISTINCT only apply to primary key?

Here's a simplified extract from my code: SELECT EmailAddress,First_Name,Last_Name,Address_ID, Customer_Key(PRIMARY KEY) FROM table_1


Regardless whether in Marketing Cloud or outside, this is how DISTINCT works:

-SELECT DISTINCT returns only distinct (different) values.

-SELECT DISTINCT eliminates duplicate records from the results.

-DISTINCT can be used with aggregates: COUNT, AVG, MAX, etc.

-DISTINCT operates on a single column. DISTINCT for multiple columns is not supported.

Source: https://www.dofactory.com/sql/select-distinct

This mens it will only make sense to use distinct with queries like SELECT DISTINCT Customer_Key FROM table_1. If you use it in a query with more columns, it will only deduplicate rows where all field values are identical.

To be able to deduplicate queries with multiple columns, use the GROUP BY clause: https://www.w3schools.com/sql/sql_groupby.asp

  • Am I crazy or does it not return a distinct row across all columns listed in the SELECT clause (you're not limited to just one), as opposed to just a single column? Nov 6 '19 at 15:11
  • I'm telling you guys, when I use a DISTINCT and the resulting Data Extension has a PrimaryKey field (I'm using overwrite), MC fetches the first PK field, and skips the subsequent duplicates. I'm I crazy or what? I checked 100 times and indeed it does skip duplicate PKs.
    – nix9247
    Nov 19 '19 at 12:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.