Google
Google Interview Questions | SQL Server Software | Microsoft SQL Server Books | Denis Gobo's personal blog | Pro SQL server 2005 (Apress)
Top 10 Articles | Add Yourself To My Frappr Map | Ken Henderson Interview | Louis Davidson Interview

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

Labels:

5 Comments:

Blogger DamienG said...

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

7:16 AM  
Blogger Jon said...

Helpful. Thank you.

12:52 PM  
Anonymous Anonymous said...

Pretty good stuff...

11:34 AM  
Anonymous Anonymous said...

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

3:19 PM  
Blogger SG said...

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

Thanks.
MRF1526@gmail.com

6:35 AM  

Post a Comment

<< Home