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
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 |