急！急！急！排序问题

[zhousq00]

表TB
ID T1 T2
1 2 11
2 4 12
3 3 14
4 6 13
5 5 15
6 3 17
7 5 10

现在要排序成这样：
ID T1 T2
6 3 17
3 3 14
5 5 15
7 5 10
4 6 13
2 4 12
1 2 11

即：先找T2最大的，然后把T1一样的放一起，再找T2次大的，也把T1一样的放一起

这个排序语句要怎么写啊？

[华夏小卒]

--> 测试数据: #tb
if object_id('tempdb.dbo.#tb') is not null drop table #tb
go
create table #tb (ID int,T1 int,T2 int)
insert into #tb
select 1,2,11 union all
select 2,4,12 union all
select 3,3,14 union all
select 4,6,13 union all
select 5,5,15 union all
select 6,3,17 union all
select 7,5,10


select a.* from #tb a,
(
	select *,px=row_number()over(order by t2 desc) 
	from #tb t 
	where not exists(select * from #tb where t1=t.t1 and t2>t.t2) 
)b
where a.t1=b.t1
order by px,t2 desc
 

ID          T1          T2
----------- ----------- -----------
6           3           17
3           3           14
5           5           15
7           5           10
4           6           13
2           4           12
1           2           11

(7 行受影响)

[xman_78tom]

1> with tb(id,t1,t2) as (
2>  select 1,2,11 union all select 2,4,12 union all
3>  select 3,3,14 union all select 4,6,13 union all
4>  select 5,5,15 union all select 6,3,17 union all
5>  select 7,5,10
6> ),
7> t as (
8> select id,t1,t2,(select max(t2) from tb where b.t1=t1) t3 from tb b
9> )
10> select id,t1,t2 from t order by t3 desc,t2 desc;
11> go
id          t1          t2
----------- ----------- -----------
          6           3          17
          3           3          14
          5           5          15
          7           5          10
          4           6          13
          2           4          12
          1           2          11

(7 rows affected)

[叶子]

declare @表TB table (ID int,T1 int,T2 int)
insert into @表TB
select 1,2,11 union all
select 2,4,12 union all
select 3,3,14 union all
select 4,6,13 union all
select 5,5,15 union all
select 6,3,17 union all
select 7,5,10

select a.* from @表TB a
left join (
select T1,max(T2) as T2 from @表TB group by T1
)b on a.T1=B.T1 
ORDER BY B.T2 DESC,A.T2 DESC,B.T1 
/*
ID          T1          T2
----------- ----------- -----------
6           3           17
3           3           14
5           5           15
7           5           10
4           6           13
2           4           12
1           2           11
*/

[-晴天]

create table tb(ID int,T1 int,T2 int)
insert into tb select 1,2,11
insert into tb select 2,4,12
insert into tb select 3,3,14
insert into tb select 4,6,13
insert into tb select 5,5,15
insert into tb select 6,3,17
insert into tb select 7,5,10
go
;with c1 as(
select ROW_NUMBER() over(order by t2 desc) rn,* from tb
),c2 as(
select a.rn,b.id,b.t1,b.t2 from c1 a inner join c1 b on a.T1=b.T1
),c3 as(
select ID,t1,t2,min(rn)rn from c2 group by ID,T1,T2
)select id,t1,t2 from c3 order by rn,t2 desc
go
drop table tb
/*
id          t1          t2
----------- ----------- -----------
6           3           17
3           3           14
5           5           15
7           5           10
4           6           13
2           4           12
1           2           11

(7 行受影响)

*/