Another way to do this is using Indentity, not using it as a property of a column, but as function in a TSQL statement. Using your example:
select Identity(int,1,1) as rank, Hiredate, LastName, Firstname
where Title = 'Sales Representative'
order by HireDate
Select cast(rank as char(4)) as Rank,
cast(hiredate as varchar(23)) as HireDate,
FirstName from #HireDate order by 1
Drop table #HireDate
I think this could be a good function to have in mind, specially when you want a quickly solution.