Thursday, March 23, 2006

Ranking In SQL Server 2000

SQL server 2005 has 4 new ranking/windowing functions
These functions are RANK(), DENSE_RANK(), NTILE() and ROW_NUMBER()
I will show you tomorrow how you can use these new functions, today I will show you all the hard work you have to do to accomplish the same in SQL Server 2000
I am only going to show how to implement RANK(), DENSE_RANK() and ROW_NUMBER() in SQL Server 2000

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'


So let's start with ROW_NUMBER()
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

ROW_NUMBER()
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


Next up is DENSE_RANK()
Duplicates are considered and same values have the same number, numbers are not skipped

DENSE_RANK()
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


And last we have RANK()
Duplicates are considered and same values have the same number, however numbers are skipped

RANK()
SELECT z.Ranking ,t2.Value
FROM (SELECT MIN(t1.Rank) AS Ranking,t1.Value FROM #Ranks t1 GROUP BY t1.Value) z
JOIN #Ranks t2 ON z.Value = t2.Value
ORDER BY z.Ranking


That is it, tomorrow I will do the SQL Server 2005 version of this code

7 comments:

Anonymous said...

Seeing the output of this would be quite interesting instead of having to fire off SQL itself to find out.

Jon Sagara said...

Helpful. Thank you.

Anonymous said...

Pretty good stuff...

Anonymous said...

prety..prety..prety good!!!
it saved my life!! THANKS!

Unknown said...

Hi.
this Way is very simple .
help me to write A Rank function Query .

Thanks.
MRF1526@gmail.com

nikko said...

sir what is the use of the "#Ranks" ?

i'm a bit confused.. i tried to use your code in another table but it did'nt work i have changed #ranks to a tablename

Anonymous said...

This does not work when I use ID as identity , I am using Oracle 10g