I have 1st DataExtension names "Accessories" and 2nd as "TestAccessories". I want to dump all the unique records from Accessories to TestAccessories, My query is as follows:

SELECT DISTINCT [Customer Code], Email, Location, [Operator Code], Date, Time, [Customer Group1], GUID
FROM Accessories

In Accessories DE GUID is Primary key, and I want unique values from [customer code] field, the above given query is not giving me unique values.What is the other way?

I have tried the same query for finding Distinct emails but it's not giving required output.

  • Have you tried using just one field, the one that needs to be unique, like SELECT DISTINCT [Customer Code] FROM Accessories – Hemant Jain Mar 22 '18 at 6:26
  • Yes, I did try this also but it's not working as required. – Prisha Mar 22 '18 at 7:39
  • I have tried it. This works for me: SELECT DISTINCT Field as UUID FROM dataExtension1. The as UUID is only to match it on the field on the target data extension – Johannes Schapdick Mar 22 '18 at 8:09
  • GUID's are already unique fields so its of no use to filter data based on GUID. – Prisha Mar 22 '18 at 11:48

AFAIK you will get every item out of this because you use distinct over all of your fields instead of only one column.

Workaround, because i dont know the better way atm:

  1. Create a DataExtension called DISTINCTCUSTOMERCODES with the field CustomerCode
  2. Create an SQL Query Automation SELECT DISTINCT [Customer Code] as CustomerCode FROM Accessories into the new Data Extension DISTINCTCUSTOMERCODES
  3. Create a Second SQL Query ActivitySELECT DISTINCT [Customer Code], Email, Location, [Operator Code], Date, Time, [Customer Group1], GUID FROM Accessories as acc INNER JOIN DISTINCTCUSTOMERCODES as dcc ON acc.CustomerCode= dcc.CustomerCode
  4. Create an Automation that runs those activities after each other

I know that there is a better solution for the SQL Statement. I believe (not certain) you have to do something like this. I know the Syntax is wrong on the JOIN statement atleast, feel free to correct this.

SELECT [Customer Code], Email, Location, [Operator Code], Date, Time, [Customer Group1], GUID
FROM Accessories as acc1
INNER JOIN Accessories as acc2
(SELECT DISTINCT [Customer Code]
FROM Accessories as acc2)
ON acc1.[Customer Code]= acc2.[Customer Code]
  • This isn't useful because I have only 1 data extension which contains records and I want to dump the data in the 2nd data extension. ('Customer Code" is a field in the data extension). – Prisha Mar 22 '18 at 11:47
  • Got one mistake in one query in my workaround. Are you sure that you dont want to create a new dataextension like i Said in the workaround? It is working and i tested it aswell. – Johannes Schapdick Mar 23 '18 at 4:20
  • @Prisha you need only query the [Customer Code] filed or GUID field also if you need only [Customer Code] field means use this query like this Select DISTINCT Customer Code from Accessories its working fine – pkharries Mar 23 '18 at 7:47

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.