i need find users have same name, surname , date of birth. have include each one's client id can't use count(*)
group by. using row_number()
partitioned 3 columns in cte. need include multiple values including where rownumber = 1
.
this have now:
;with rownumbers ( select [client code] ,name ,surname ,[date of birth] ,row_number() on (partition name ,surname ,[date of birth] order [client code] )as [row number] kyc_details ) select ,[client code] ,name ,surname ,[date of birth] ,[row number] [rownumbers] [row number] > 1
the issue here is, need include rownumber = 1
. in essence need select columns duplicate values client code unique.
here answer (i used count on partitioned by):
;with rownumbers ( select [client code],name, surname, [date of birth] ,count(*) on (partition kd.name, kd.surname,[date of birth]) total kyc_details kd ) select ,[client code] ,name ,surname ,[date of birth] ,total rownumbers total > 1 order name ,surname
Comments
Post a Comment