0

I'm using the below query to fetch only the unique "emailid" field value but still I'm getting duplicates

SELECT  Distinct Emailid , EmailName 
FROM _job  a
WHERE a.SuppressTracking=0
and a.EmailName not like '%welcome%'
union 
select Distinct Emailid , EmailName FROM 
ent.[Sentio Tagging] b 
where b.AccountID = 6323126 

In Sentio Tagging table , there are no duplicate records, the duplicate records are coming from _job only

Can anyone please assist me.

  • You don't need "Distincts" in your queries as you're using a union query. What's the PrimaryKey on your target data extension and how's Sentio Tagging populated? Could it be that you have EmailNames that differ between the two sources? Eg: JobID 123 has the EmailName "ABC" in _job and "XYZ" in Sentio Tagging? – Macca Aug 5 at 2:21
  • Hi @Macca thanks for taking a look, We are not using any primarykey as our query is update query. We don't have JOBID field in sentio tagging table. Only Accountid, Emailid,Emailname are the matching fields in the both the tables. We need only one record for each emailid (for ex: only the last time send ) – sai Aug 5 at 2:26
  • You'd have a Primary Key on your target DE or you'd not be able to do an update query, only overwrites. Anyhow, I'll drop an answer that gives you one row per EmailId. – Macca Aug 5 at 2:52
2

This is kind of clunky and would be far more efficient if you populated your Sentio Tagging using SFMC's EmailIds and Names, such that you could use a plain UNION query. But this should work for you...

SELECT 
    y.Emailid,
    y.EmailName
FROM 
    (
    SELECT 
        ROW_NUMBER() OVER(PARTITION BY Emailid ORDER BY x.CreatedDate DESC) AS RowNumber,
        x.Emailid,
        x.EmailName
    FROM 
        (
          SELECT
              Emailid,
              EmailName,
              CreatedDate
          FROM _job
          WHERE
              SuppressTracking=0 AND EmailName NOT LIKE '%welcome%'
          UNION ALL
          SELECT
              Emailid,
              EmailName,
              CONVERT(DateTime,'1900-01-01T00:00:00') AS CreatedDate
          FROM ent.[Sentio Tagging]
          WHERE
              AccountID = 6323126    
        ) AS x
    ) AS y
WHERE
    y.RowNumber = 1
| improve this answer | |
  • Thanks a lot, It is working :) – sai Aug 5 at 4:59

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