Row Number
From Wiki
Row Number(SQL Server 2000)
Since we have duplicates we can't do a running count, we will use that for DENSE_RANK Duplicates are not considered, each row has a unique number Since SQL Server 2000 doesn't have the windowing functions which are available in SQL server 2005 we have to take a different approach.
First create this table
- CREATE TABLE Rankings (VALUE CHAR(1))
- INSERT INTO Rankings
- SELECT 'A' UNION ALL
- SELECT 'A' UNION ALL
- SELECT 'B' UNION ALL
- SELECT 'B' UNION ALL
- SELECT 'B' UNION ALL
- SELECT 'C' UNION ALL
- SELECT 'D' UNION ALL
- SELECT 'E' UNION ALL
- SELECT 'F' UNION ALL
- SELECT 'F' UNION ALL
- SELECT 'F'
To do the equivalent of the Dense_Rank function in SQL Server 2000 we have to use a temp table with an identity column.
- SELECT IDENTITY(INT, 1,1) AS Rank ,VALUE
- INTO #Ranks FROM Rankings WHERE 1=0
- INSERT INTO #Ranks
- SELECT VALUE FROM Rankings
- ORDER BY VALUE
- SELECT * FROM #Ranks
After we have the values in the table it is as simple as this:
Row Number(SQL Server 2005/2008)
We are starting out by creating a table
- CREATE TABLE Rankings (VALUE CHAR(1),id INT)
- INSERT INTO Rankings
- SELECT 'A',1 UNION ALL
- SELECT 'A',3 UNION ALL
- SELECT 'B',3 UNION ALL
- SELECT 'B',4 UNION ALL
- SELECT 'B',5 UNION ALL
- SELECT 'C',2 UNION ALL
- SELECT 'D',6 UNION ALL
- SELECT 'E',6 UNION ALL
- SELECT 'F',5 UNION ALL
- SELECT 'F',9 UNION ALL
- SELECT 'F',10
This will just add a plain vanilla row number
- SELECT ROW_NUMBER() OVER( ORDER BY VALUE ) AS 'rownumber',*
- FROM Rankings
The following one is more interesting, besides the rownumber the Occurance field contains the row number count for a given value That happens when you use PARTITION with ROW_NUMBER
- SELECT ROW_NUMBER() OVER( ORDER BY VALUE ) AS 'rownumber',
- ROW_NUMBER() OVER(PARTITION BY VALUE ORDER BY ID ) AS 'Occurance',*
- FROM Rankings
- ORDER BY 1,2
This is just ordered in alphabetical order descending
- SELECT ROW_NUMBER() OVER( ORDER BY VALUE DESC) AS 'rownumber',*
- FROM Rankings
Contributed by: --SQLDenis 02:36, 31 May 2008 (GMT)
Part of SQL Server Programming Hacks
Section Ranking, Max etc


