Ranking functions in SQL Server
Posted by chandru14 on July 27, 2008
Hello friends J,
SQL Server 2005 has introduced some new ranking functions which make our job more easy when ever we want to assign ranks to employees / stundets based on different criteria.
Some of us might argue over that we can achieve the same ranking using ROW_NUMBER() function but the problem here is it always provides sequential values like 1,2,3,4 etc even though two are more records have the same ranking criteria. To avoid such issues, we can use RANK() or DENSE_RANK() based on the requirement.
I hope you find this article useful for you J. Here is the example.
Click here to download the script
Example:-
CREATE TABLE Dept( Dept_id INT PRIMARY KEY, DeptName VARCHAR(100), Loc VARCHAR(100))
GO
INSERT INTO Dept VALUES(101,‘Admin’,‘Chennai’)
INSERT INTO Dept VALUES(102,‘Sales’,‘Hyderabad’)
INSERT INTO Dept VALUES(103,‘Operations’,‘Delhi’)
INSERT INTO Dept VALUES(104,‘IT’,‘Banglore’)
GO
CREATE TABLE Employee(Emp_id INT PRIMARY KEY, EmpName VARCHAR(100),DOJ DATETIME, Salary MONEY, Dept_id INT REFERENCES Dept(Dept_id))
GO
INSERT INTO Employee VALUES(1001,‘Chandra’,‘15-Dec-2004′,10000,104)
INSERT INTO Employee VALUES(1002,‘VVS’,‘01-Jan-2005′,30000,104)
INSERT INTO Employee VALUES(1003,‘Pagalavan’,‘15-Oct-2005′,40000,104)
INSERT INTO Employee VALUES(1004,‘ASN’,‘10-Feb-2006′,20000,104)
INSERT INTO Employee VALUES(1005,‘Sathish’,‘19-Dec-2005′,10000,104)
INSERT INTO Employee VALUES(1006,‘Sri’,‘06-Sep-2007′,50000,104)
INSERT INTO Employee VALUES(1007,‘Sangeetha’,‘10-Nov-2007′,15000,104)
INSERT INTO Employee VALUES(1008,‘Manick’,‘25-Jan-2006′,20000,104)
INSERT INTO Employee VALUES(1009,‘Kanchana’,‘15-Feb-2005′,19000,104)
INSERT INTO Employee VALUES(1010,‘Arun’,‘15-Mar-2005′,12000,103)
INSERT INTO Employee VALUES(1011,‘Zahed’,‘05-Apr-2004′,11000,103)
INSERT INTO Employee VALUES(1012,‘Rajesh’,‘14-May-2005′,60000,103)
INSERT INTO Employee VALUES(1013,‘Ila’,‘15-Jun-2006′,80000,102)
INSERT INTO Employee VALUES(1014,‘Remya’,‘15-Jul-2006′,40000,102)
INSERT INTO Employee VALUES(1015,‘Tito’,‘15-Aug-2008′,70000,102)
INSERT INTO Employee VALUES(1016,‘RP’,‘11-Sep-2007′,30000,102)
INSERT INTO Employee VALUES(1017,‘Khiroj’,‘18-Oct-2008′,40000,102)
INSERT INTO Employee VALUES(1018,‘Gopi’,‘20-Nov-2005′,60000,101)
INSERT INTO Employee VALUES(1019,‘Venkat’,‘12-Dec-2007′,10000,101)
INSERT INTO Employee VALUES(1020,‘JJR’,‘12-Jan-2006′,15000,101)
INSERT INTO Employee VALUES(1021,‘Shivan’,‘15-Feb-2008′,25000,101)
GO
/* Either of the following queries provide the rank for each employee based on salary.*/
SELECT Top 5 *, RANK() OVER (ORDER BY Salary DESC) AS Rank FROM Employee
Output :-
|
Emp_id |
EmpName |
DOJ |
Salary |
Dept_id |
Rank |
|
1013 |
Ila |
6/15/2006 |
80000 |
102 |
1 |
|
1015 |
Tito |
8/15/2008 |
70000 |
102 |
2 |
|
1012 |
Rajesh |
5/14/2005 |
60000 |
103 |
3 |
|
1018 |
Gopi |
11/20/2005 |
60000 |
101 |
3 |
|
1006 |
Sri |
9/6/2007 |
50000 |
104 |
5 |
SELECT Top 5 *, DENSE_RANK() OVER (ORDER BY Salary DESC) AS Rank FROM Employee
Output :-
|
Emp_id |
EmpName |
DOJ |
Salary |
Dept_id |
Rank |
|
1013 |
Ila |
6/15/2006 |
80000 |
102 |
1 |
|
1015 |
Tito |
8/15/2008 |
70000 |
102 |
2 |
|
1012 |
Rajesh |
5/14/2005 |
60000 |
103 |
3 |
|
1018 |
Gopi |
11/20/2005 |
60000 |
101 |
3 |
|
1006 |
Sri |
9/6/2007 |
50000 |
104 |
4 |
Using Partition BY clause:-
/* Either of the following queries provide the rank for each employee based on salary grouped by department*/
SELECT *, RANK() OVER (PARTITION BY Dept_id ORDER BY Salary DESC) AS Rank
FROM Employee
GO
SELECT *, DENSE_RANK() OVER (PARTITION BY Dept_id ORDER BY Salary DESC) AS Rank
FROM Employee
Note:-
There is a slight difference between RANK() and DENSE_RANK().
In case of RANK() function If two or more rows tie for a rank, each tied rows receives the same rank.
For example, if the two top employees have the same salary, they are both ranked one. The employee with next highest salary is ranked number three
In case of DENSE_RANK() If two or more rows tie for a rank , each tied rows receives the same rank.
For example, if the two top employees have the same salary, they are both ranked one. The employee with the next highest salary is ranked number two.
