OK, MS is out of date; using a subquery is faster. Like this:

Select t.tname, t.tid,

(

Select count(t1.tid)

From #test t1

where t1.tName < t.tName--This is where you signify the rank order

) NameRank

From #test t

----------------Test Data------------------

if object_ID('tempdb..#test') is not null drop table #test

create table #test (Tid int identity, tname char(3) Not Null Primary Key)

Insert #test Values ('ZZZ')

Insert #test Values ('GGG')

Insert #test Values ('HHH')

Insert #test Values ('III')

Insert #test Values ('DDD')

Insert #test Values ('CCC')

Insert #test Values ('BBB')

Insert #test Values ('AAA')