order by case n1 when 'g' then '0' when 'c' then '1' else '9' end + n1
select * from t1 order by case n1 when 'g' then 0 when 'c' then 1 else 2 end
declare @T1 table(n1 varchar(5),n2 int) insert into @T1 select 'a',1 union all select 'c',1 union all select 'b',1 union all select 'd',1 union all select 'f',1 union all select 'g',1 union all select 'g',2 union all select 'a',1 select *,'n3'=(case n1 when 'g' then 2 when 'c' then 1 end),'n4'=(case when n1 ='g' or n1 ='c' then 1 else 2 end) from @T1 order by n4 asc,n3 desc,n1 asc/* n1 n2 n3 n4 -------------------- g 1 2 1 g 2 2 1 c 1 1 1 a 1 NULL 2 a 1 NULL 2 b 1 NULL 2 d 1 NULL 2 f 1 NULL 2 */
when 'g' then '0'
when 'c' then '1'
else '9' end + n1
declare @T1 table(n1 varchar(5),n2 int)
insert into @T1
select 'a',1 union all
select 'c',1 union all
select 'b',1 union all
select 'd',1 union all
select 'f',1 union all
select 'g',1 union all
select 'g',2 union all
select 'a',1
select *,'n3'=(case n1 when 'g' then 2 when 'c' then 1 end),'n4'=(case when n1 ='g' or n1 ='c' then 1 else 2 end) from @T1
order by n4 asc,n3 desc,n1 asc/*
n1 n2 n3 n4
--------------------
g 1 2 1
g 2 2 1
c 1 1 1
a 1 NULL 2
a 1 NULL 2
b 1 NULL 2
d 1 NULL 2
f 1 NULL 2
*/