Geek Answers Handbook

SQL: numbering rows returned by a SELECT statement

Suppose I have a SELECT statement that returns some set of results. Is there a way I can calculate my results as follows:

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, commas mean the numbers are in their own column. [I am using SQL Server 2000.]

+4
source share
6 answers

In Microsoft SQL Server 2005, you have a ROW_NUMBER() function that does exactly what you want.

If you are stuck with SQL Server 2000, the typical technique was to create a new temporary table containing the result of your query, plus add an IDENTITY column and generate incremental values. See the article that talks about this technique here: http://www.databasejournal.com/features/mssql/article.php/3572301/RowNumber-function-in-SQL-Server-2005.htm

+5
source

With SQL 2000, you need to use a correlated subquery.

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

It is usually easier to add numbers to the client application. There are tricks in SQL, but for a purist, they include cheating, and they are usually not portable.

For me, this is one of my most basic refactoring patterns.

+2
source

You can also do this using the 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 notice, there will be a SELECT statement. It is just surrounded by things that make line numbers work.

+2
source

You can use this query, which PK takes into account, to ensure proper numbering in the case of the 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
+1
source

SQL 2005, 2008:

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

EDIT: Sorry - you saw 2000.

0
source

More articles:


All Articles