Q:

Skip records with duplicate Primary Key while querying data using SQL activity in SFMC

I am trying to query some specific email activities using SQL Activity and Data Views in SFMC. I am trying to fetch data from Subscriber, Sent and Job data view. I need EmailName, EmailAddress, SubscriberKey,EventDate and JobID field in the query results. Following is the the SQL Activity:
SELECT sub.SubscriberKey AS SubscriberKey,
       sub.EmailAddress AS EmailAddress,
       job.EmailName AS EmailName,
       sen.EventDate AS ActivityDate,
       sen.JobID AS JobID
FROM   ent._Subscribers AS sub
       INNER JOIN _Sent AS sen
               ON sub.SubscriberKey = sen.SubscriberKey
       INNER JOIN _Job AS job
               ON sen.JobID = job.JobID
WHERE  job.EmailName LIKE '%ABC%'

I am using SubScriberKey and JobId as a primary key in target data extension. UPDATE has been chosen as Data Action on the SQL Activity. After executing SQL query found that data view has some duplicate records i.e. same email has been sent to same subscriber as part of same send job so SubscriberKey and JobID is duplicate on some records. Because of this SQL query fails with "Error: Violation of PRIMARY KEY constraint. Cannot insert duplicate key in object" error.
I basically don't want the duplicate records in my query result. Is there a way to skip duplicate records in query results?

A:

The primary key on _Sent is [JobID,ListID,BatchID,SubscriberID], so you can either change the primary key on your results table to avoid the error, you can use a GROUP BY clause in your query or use a PARTITION BY clause in a subquery and restrict rows returned after partitioning.
GROUP BY
SELECT sub.SubscriberKey AS SubscriberKey,
       sub.EmailAddress AS EmailAddress,
       job.EmailName AS EmailName,
       MAX(sen.EventDate) AS ActivityDate,
       sen.JobID AS JobID
FROM   ent._Subscribers AS sub
       INNER JOIN _Sent AS sen
               ON sub.SubscriberKey = sen.SubscriberKey
       INNER JOIN _Job AS job
               ON sen.JobID = job.JobID
WHERE  job.EmailName LIKE '%ABC%'
GROUP BY
        sub.SubscriberKey,
        sub.EmailAddress,
        job.EmailName,
        sen.JobID

PARTITION BY
SELECT x.*
FROM 
    (
      SELECT
              ROW_NUMBER() OVER(PARTITION BY sub.SubscriberKey,sen.JobID ORDER BY sen.EventDate DESC) AS RowNumber,
              sub.SubscriberKey AS SubscriberKey,
              sub.EmailAddress AS EmailAddress,
              job.EmailName AS EmailName,
              sen.EventDate AS ActivityDate,
              sen.JobID AS JobID
      FROM    ent._Subscribers AS sub
              INNER JOIN _Sent AS sen
                     ON sub.SubscriberKey = sen.SubscriberKey
              INNER JOIN _Job AS job
                     ON sen.JobID = job.JobID
      WHERE   job.EmailName LIKE '%ABC%'    
    ) AS x
WHERE 
  x.RowNumber = 1

