高难度排序问题，急寻帮助！

[tt3125]

比如有一表TTT有几个字段
ID
cde
cdeid
pid


数据如下

ID cde cdeid pid
1 AAA 2 2
2 DDD 1 3
3 CCC 1 1
4 DDD 3 1
5 DDD 2 1
6 DDD 1 2

每次搜索都显示表TTT中全部数据，搜索时POST传递数据cde值
例如搜索时cde=DDD
排序的规则是:
1、获取cde=DDD数据并且优先cdeid按从大到小排序然后pid按小到大排序
2、cde不等于DDD的数据cdeid不排序，pid按小到大排序

如果能满上二条应该搜索如下结果：
ID cde cdeid pid
4 DDD 3 1
5 DDD 2 1
6 DDD 1 2
2 DDD 1 3
3 CCC 1 1
1 AAA 2 2



我能想到的写法：
传数据cde=DDD

1、
select cde,cdeid,pid from TTT where cde="&request("cde")&" order by cdeid desc,pid asc
这样搜索结果只显示能cde=DDD数据并且优先cdeid按从大到小排序然后pid按小到大排序，不能显示全部数据

2、
select cde,cdeid,pid from TTT order by pid asc
这样又不能满足“1、获取cde=DDD数据并且优先cdeid按从大到小排序然后pid按小到大排序”


急寻帮助！

[子陌红尘]

select 
    cde,cdeid,pid 
from 
    TTT 
order by 
    (case cde when 'DDD' then cdeid else 0 end) desc,pid asc 

[chuifengde]

select * from TTT order by case when cde='DDD' then 0 else 1 end,case when cde='DDD' then cdeid else 0 end desc,pid

[tim_spac]

方案一:
用两个select分别获得:
cde="&request("cde")&" order by cdeid desc,pid asc
cde!="&request("cde")&" order by pid asc
用union将两个结果集合并

方案二:
select cde,cdeid,pid from TTT order by
case cde!="&request("cde")&" then -1 else cdeid end desc, pid asc

[bqb]

create table test(ID int, cde varchar(20), cdeid int, pid int)
insert into test
select 1 , 'AAA' , 2 , 2 union all
select 2 , 'DDD' , 1 , 3 union all
select 3 , 'CCC' , 1 , 1 union all
select 4 , 'DDD' , 3 , 1 union all
select 5 , 'DDD' , 2 , 1 union all
select 6 , 'DDD' , 1 , 2

select * from test
order by
case when cde='DDD' then '1' else '0' end+
case when cde='DDD' then convert(char(1),cdeid) else '0' end desc,pid


/*
ID cde cdeid pid
--------------------------------------------------
4 DDD 3 1
5 DDD 2 1
6 DDD 1 2
2 DDD 1 3
3 CCC 1 1
1 AAA 2 2
*/