Delete duplicate records by column 1 but having differ values in column 2 and column 3 I have a table like:

SSN FName   LName
1   a   b
1   a   b
1   b   b -------------&gt; to delete
2   c   d
2   c   d


as above example I want to delete such records where SSN has different values in FName and LName columns.

Kindly help.
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.