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


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