Include rows in ROW_NUMBER() where greater than 1 including the first value

I need to find users that have the same name, surname and date of birth. I have to include each one's client ID as well so I can't use COUNT(*) with group by. I am using ROW_NUMBER() partitioned by those three columns in a CTE. But I need to include all the multiple values including where rownumber = 1.

This is what I have now:

;WITH 
Rownumbers AS
(
    SELECT 
        [Client code]
        ,Name
        ,Surname
        ,[Date of Birth]
        ,ROW_NUMBER() OVER
            (PARTITION BY 
                name
                ,surname
                ,[DATE of birth]
            ORDER BY 
                [client code]
            )AS [Row Number]

    FROM 
        kyc_details
)

SELECT 
,[client code]
,Name
,Surname
,[DATE of birth]
,[Row Number] 
FROM 
[Rownumbers]
WHERE
[Row Number] > 1

The issue here is, I need to include the rownumber = 1. In essence I need to select all the columns with duplicate values but the client code will be unique.

Upvotes: 1

Views: 5549

Answers (1)

Here is the answer (I used a count over partitioned by):

;WITH 
rownumbers AS 
(
    SELECT 
        [Client code],name, surname, [date of birth]
        ,COUNT(*) OVER (PARTITION BY  kd.name, kd.Surname,[DATE of birth]) AS total
FROM 
    kyc_details AS kd 

   )

SELECT 
,[client code]
,Name
,Surname
,[DATE of birth]
,total  
FROM 
rownumbers
WHERE 
total > 1   
ORDER BY 
Name
,surname    

Upvotes: 6

Related Questions