How to dynamicaly rank rows

select rank=count(*), a1.au_lname, a1.au_fname
from authors a1, authors a2
where a1.au_lname + a1.au_fname >= a2.au_lname + a2.au_fname
group by a1.au_lname, a1.au_fname
order by 1

-- groups:
Declare @data table(idn int, diag int, recCount int)
insert into @data
Select 1, 42, 75 union
Select 1, 49, 50 union
Select 1, 38, 22 union
Select 2, 70, 48 union
Select 2, 33, 27

select a.*,
(select count(*)
from @data b
where a.idn = b.idn and a.RecCount >= b.RecCount) as Rank
from @data a

Source: https://weblogs.sqlteam.com/mladenp/2005/08/01/7421/
Viewed 12843 times