Delete duplicate records by column 1 but having differ values in column 2 and column 3
It looks like you want to keep the rows where the combination of SSN, FName and LName are the most common.

You can do that with an updateable CTE that counts the number of occurrences for each combination using count() over() and then enumerates the counts using rank().

declare @T table
(
  SSN int,
  FName char(1),
  LName char(1)
);

insert into @T values
(1, 'a', 'b'),
(1, 'a', 'b'),
(1, 'b', 'b'), -------------&gt; to delete
(2, 'c', 'd'),
(2, 'c', 'd'),
(3, 'a', 'b'),
(3, 'c', 'd');

with C1 as
(
  select T.SSN,
         count(*) over(partition by T.SSN, T.FName, T.LName) as C
  from @T as T
), C2 as
(
  select rank() over(partition by C1.SSN order by C1.C desc) as RN
  from C1
)
delete from C2
where C2.RN &gt; 1;

select *
from @T;


Result:

SSN         FName LName
----------- ----- -----
1           a     b
1           a     b
2           c     d
2           c     d
3           a     b
3           c     d


In the case where there is a tie for most common combination (SSN = 3) the above query will keep all the rows for the tie.