create table emp(Employeeid int, Employeename nvarchar(30), [Relation ship] nvarchar(50))
insert into emp values(16, 'Malliah', 'Self')
insert into emp values(25, 'vishnu', 'Son')
insert into emp values(25, 'Narayana Murthy Konathala', 'Husband')
insert into emp values(25, 'Tulasi', 'Self')
insert into emp values(16, 'NageswaraRao', 'Father')
insert into emp values(16, 'Vivek', 'Son')
select Employeeid, Employeename, [Relation Ship] from
(
select Employeeid, Employeename, [Relation Ship],
CASE
WHEN [Relation ship]= 'Self' THEN 1
WHEN [Relation ship]= 'Father' THEN 2
WHEN [Relation ship]= 'Husband' THEN 2
WHEN [Relation ship]= 'Son' THEN 3
END Col4
from emp
)x order by Employeeid, Col4 ASC
RESULT
Employeeid Employeename Relation Ship
----------- ------------------- ----------------------------------------
16 Malliah Self 1
16 NageswaraRao Father 2
16 Vivek Son 3
25 Tulasi Self 1
25 Narayana Murthy Konathala Husband 2
25 vishnu Son 3
(6 row(s) affected)