Rank function in SQL Server 2005 ...

This article is to explain the functionality of Rank() function in SQL Server 2005. In our schooling days if the over all score of two guys are the same in exams then they would be ranked as 1 each and the next guy would get 3rd rank and not 2nd rank. To achieve same kind of result in SQL Server 2005 we can make use of this Rank() function.

Extract from MSDN:

If two or more rows tie for a rank, each of the tied rows receives the same rank. For example, if the two top sales people have the same SalesYTD value, then they are both ranked one. The sales person with the next highest SalesYTD is ranked number three, since there are two rows that are ranked higher. Therefore, the RANK function does not always return consecutive integers.

Table Structure for the sample table:

Create table DemoRankingFunction
(
Sno int identity,
FirstName varchar(25),
Marks int
)

Sample records for the demo purpose:

Insert into DemoRankingFunction Values ('Vadivel',99)
Insert into DemoRankingFunction Values ('Sai',99)
Insert into DemoRankingFunction Values ('Gowri',90)

Insert into DemoRankingFunction Values ('Rajini',86)
Insert into DemoRankingFunction Values ('Kamal',80)
Insert into DemoRankingFunction Values ('Sneha',80)
Insert into DemoRankingFunction Values ('Simran',76)

Let us write a query which would help us rank the students according to the marks scored.

Select
Sno,
FirstName,
Marks,
Rank() Over (Order by Marks desc)
From
DemoRankingFunction

Result:

Sno FirstName Marks Result
----- ------------ ------- --------
1 Vadivel 99 1
2 Sai 99 1
3 Gowri 90 3
4 Rajini 86 4
5 Kamal 80 5
6 Sneha 80 5
7 Simran 76 7

Clean up:

Delete table DemoRankingFunction

Technorati tags: SQL, Databases, SQL Server, SQL Server 2005