Select Statement With A New Identity Field

Hello,

 Is it possible to generate a identityfield dynamically upon select, like this:

SELECT tempID AS identity(1,1), username FROM table1 ORDER BY username ASC

I want the output to be:

1 - Name1
2 - Name2
3 - Name3

The reason for this, is that i want to change the sort order in many diffrent ways, but i need to get the IDs from 1-?? even when the sort order changes.

Like:

SELECT tempID AS identity(1,1), username FROM table1 ORDER BY username DESC

I want the output to be:

1 - Name3
2 - Name2
3 - Name1

 

Patrick