Возвращение порядковых номеров строк в результирующем наборе

Начиная с 2005 сервера
select rank() OVER (ORDER BY a.au_lname, a.au_fname) as rank, a.au_lname, a.au_fname
from authors a
order by rank

для версий до 2005
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 rank

2000 sql server и выше

declare i int
set i=0

select 0 as id, Name, Comm
into #temp
from MyTable

update #temp set @i=id=@i+1

select * from #temp

drop table #temp

/* временная таблица с полем identity */
create table #rank_table
(
	rank int identity not null,
	id_test int null,
	string char (7),
	constraint pk_test primary key (rank)
)
go
insert into #rank_table (id_test, string)
select id_test, string
from test
order by id_test
select * from #rank_table
go