SQL: Numbering the rows returned by a SELECT statement

Question

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.]

Answer 1

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