How to dynamically number rows in a SELECT Transact-SQL statement

Reference URL: http://support.microsoft.com/kb/186133

If you have to return a single record set while joining any tables, than first create a view and then apply:

Use the following code in SQL Server 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

Use the following code in SQL Server 2000.

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


You can get more details by typing "dynamically number rows site:support.microsoft.com" in google search box.