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

Dense Rank

From Wiki

Jump to: navigation, search

Dense_Rank(SQL Server 2000)

What does Rank do? Duplicates are considered and same values have the same number, numbers are not skipped 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

After we have the values in the table it is as simple as this:

tsqlLine number Off | Hide | Select all

SELECT x.Ranking ,x.VALUE
FROM (SELECT (SELECT COUNT( DISTINCT t1.VALUE) FROM Rankings t1 WHERE z.Value>= t1.VALUE)AS Ranking, z.VALUE
FROM #Ranks z ) x
ORDER BY x.Ranking