Dynamically number rows in SQL Statement

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

Courtesy: http://support.microsoft.com/kb/186133