Suppose I have a SELECT statement that returns some set of results. Is there some way I can number my results in the following way:
SELECT TOP 3 Name FROM PuppyNames ORDER BY NumberOfVotes
would give me...
Fido
Rover
Freddy Krueger
...but I want...
1, Fido
2, Rover
3, Freddy Krueger
where of course the commas signify that the numbers are in their own column. [I am using SQL Server 2000.]
In Microsoft SQL Server 2005, you have the ROW_NUMBER() function which does exactly what you want.
If you are stuck with SQL Server 2000, the typical technique was to create a new temporary table to contain the result of your query, plus add an IDENTITY column and generate incremental values. See an article that talks about this technique here: http://www.databasejournal.com/features/mssql/article.php/3572301/RowNumber-function-in-SQL-Server-2005.htm