Reputation: 83
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
Reputation: 83
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