--这样?
declare @t table(字段A varchar(10),字段B int,字段C int)
insert into @t select 'XXX',10,1
union all select '3aa',20,1
union all select 'e3aada',210,1
union all select 'feaa',230,12
union all select 'aeaa',220,13
union all select 'taa',33220,12
union all select '其他',20,1
union all select '合计',30,1
select * from @t order by case 字段A when '其他' then '1' when '合计' then '0' else 字段A end desc