Get duplicate rows only by certain fields from inner join query-CodePudding

How do I get duplicates rows only from below query?

 SELECT p.CustomerNumber
          ,pn.[Title]
          ,pn.[FirstName]
          ,pn.[LastName]
          ,a.[AgentID]
          ,a.[AgentName]
          ,a.[PersonID]
          ,pe.[EMailAddress]
          ,(SELECT TOP 1 pp.[PhoneCode] FROM [Rez].[PersonPhone] pp WHERE pp.PersonID = p.PersonID ORDER BY pp.ModifiedUTC DESC) AS Phone
      FROM [Rez].[Person] p
      INNER JOIN [Rez].[PersonName] pn ON p.PersonID = pn.PersonID
      INNER JOIN [Rez].[Agent] a ON a.PersonID = p.PersonID
      INNER JOIN [Rez].[PersonEMail] pe ON pe.PersonID = p.PersonID
      INNER JOIN [Rez].[AgentRole] ar ON ar.[AgentID] = a.[AgentID]
      WHERE a.CreatedUTC > '2018-01-01'
      ORDER BY pe.[EMailAddress], pn.[FirstName], pn.[LastName], p.[DOB]

In the above query I want to get the duplicate records by pe.[EMailAddress], pn.[FirstName], pn.[LastName], p.[DOB].

Current data:

First Name  Last Name   Agent Id    DOB          Email
server      test        2803815     11/28/2002  [email protected]
server      test        2803815     11/28/2002  [email protected]
Test        TC          2803882     11/28/2002  [email protected]
Test        AAA         2804713     11/28/2002  [email protected]
server      test        2803015     11/28/2002  [email protected]

In the above data I want to get only first 2 rows & last row becuase those are same data with same FirstName, LastName,DOB & Email address

Required Data:

server      test        2803815     11/28/2002  [email protected]
server      test        2803815     11/28/2002  [email protected]
server      test        2803015     11/28/2002  [email protected]

CodePudding user response：

You can use this method to count records in place and exclude those with only one record

SELECT * FROM
(

SELECT T1.*, 
COUNT(*) OVER (PARTITION BY 
                T1.CustomerNumber,
                T1.Title,
                T1.FirstName,
                T1.[LastName],
                T1.[AgentID],
                T1.[AgentName],
                T1.[PersonID],
                T1.[EMailAddress]
) As DuplicateRowCount

FROM
     (
      SELECT p.CustomerNumber
          ,pn.[Title]
          ,pn.[FirstName]
          ,pn.[LastName]
          ,a.[AgentID]
          ,a.[AgentName]
          ,a.[PersonID]
          ,pe.[EMailAddress]
          ,(SELECT TOP 1 pp.[PhoneCode] FROM [Rez].[PersonPhone] pp 
           WHERE pp.PersonID = p.PersonID ORDER BY pp.ModifiedUTC DESC) AS Phone
           FROM [Rez].[Person] p
           INNER JOIN [Rez].[PersonName] pn ON p.PersonID = pn.PersonID
           INNER JOIN [Rez].[Agent] a ON a.PersonID = p.PersonID
           INNER JOIN [Rez].[PersonEMail] pe ON pe.PersonID = p.PersonID
           INNER JOIN [Rez].[AgentRole] ar ON ar.[AgentID] = a.[AgentID]
           WHERE a.CreatedUTC > '2018-01-01'
     ) T1
) T2
WHERE T2.DuplicateRowCount > 1
ORDER BY T2.[EMailAddress], T2.[FirstName], T2.[LastName], T2.[DOB]