Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

Your profile

Search

Views

Row Number

From Wiki

Jump to: navigation, search

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

tsqlLine number Off | Hide | Select all

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.

tsqlLine number Off | Hide | Select all

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

tsqlLine number Off | Hide | Select all

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

tsqlLine number Off | Hide | Select all

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

tsqlLine number Off | Hide | Select all