SQL: Numbering the rows returned by a SELECT state

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

With SQL 2000 you need to use a correlated sub-query.

   SELECT (
              SELECT COUNT(*)
                FROM PuppyNames b
               WHERE b.Popularity <= a.Popularity
          ) AS Ranking
        , a.Name
     FROM PuppyNames a
 ORDER BY a.Popularity

Answer 2

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

Answer 3

SQL 2005, 2008:

SELECT TOP 3 ROW_NUMBER() OVER(ORDER BY NumberOfVotes DESC) AS VoteRank, Name FROM PuppyNames

EDIT: Sorry - just saw you specified 2000.

Answer 4

You could also do it with a temp table:

SELECT TOP 3 Name FROM PuppyNames ORDER BY NumberOfVotes DESC

becomes

CREATE TABLE #RowNumberTable (
    RowNumber int IDENTITY (1,1),
    PuppyName varchar(MAX)
)
INSERT #RowNumberTable (PuppyName)
SELECT TOP 3 Name FROM PuppyNames ORDER BY NumberOfVotes DESC
SELECT * from #RowNumberTable ORDER BY RowNumber
DROP TABLE #RowNumberTable

If you'll notice, your SELECT statement is in there. It is just surrounded by stuff that makes the row numbers work.

Answer 5

It's usually easier to add the numbers in the client app. There are tricks in SQL, but to a purist they involve cheating, and they aren't generally portable.

For me, it's one of my most basic refactoring patterns.

Answer 6

You could use this query, which takes into account the PK in order to provide correct numbering in case of same NumberOfVotes:

SELECT TOP 3 COUNT(*) AS Number, p1.Name
FROM PuppyNames AS p1 INNER JOIN PuppyNames AS p2 
    ON p1.NumberOfVotes < p2.NumberOfVotes OR (p1.NumberOfVotes = p2.NumberOfVotes AND p1.ID >= p2.ID)
GROUP BY p1.Name
ORDER BY Number