How do i create index column in sql?

Question

I'm using SQL-Server 2005.

I have table with many columns, rows i select have where clause which makes impossible to use identity as indexer. I want select all these row + indexer row (acting like identity).

example

2 jack

4 thomas

8 james

to

1 2 jack

2 4 thomas

3 8 james

thanks

Answer 1

Use rank function or row_number (http://www.databasejournal.com/features/mssql/article.php/3661461/New-Ranking-Functions-within-SQL-Server-2005.htm)

select rank() OVER (ORDER BY list of your columns) as Id, 
your_column_1, your_column_2 ...
from your table