create table #Test (Id int, Rank int)
go

insert #Test values (1, 1)
insert #Test values (1, 2)
insert #Test values (1, 3)
insert #Test values (2, 2)
insert #Test values (3, 1)
insert #Test values (3, 2)
insert #Test values (4, 1)
insert #Test values (4, 3)
go

/*
Id Rank
-- ----
1  3
1  2
1  1
4  3
4  1
2  2
3  2
3  1

*/

select t1.id, t1.rank
from #test t1 join (select id, max(rank) as maxrank from #test group by id) as t2
on t1.id = t2.id
order by t2.maxrank desc, t1.id, t1.rank desc

This page was last updated on May 01, 2006 04:28 PM.