Sql Server Rank() v/s Dense_rank()

Some days before I was googling on sql server’s system function and I got one site having sql server query puzzle. The site have very interesting puzzle. I got a puzzle during surfing and it is to find out second highest salary of each department and tie salary should also count. There are inbuilt functions in sql server to give rank as per result set.

The Rank() function of sql server returns the position of value within a partition of a result set. Rank() function left gaps where ranks are tie.

The Dense_rank() function of sql server returns the position of value within a partition of a result set. Dense_rank() function does not left gaps where ranks are tie.

Syntex :

Rank() over([partition_by] <order_by>)

Dense_rank() over([partition_by] <order_by>)

[code:sql]

DECLARE @Employees TABLE(
EmployeeID INT IDENTITY,
EmployeeName VARCHAR(15),
Department VARCHAR(15),
Salary NUMERIC(16,2)
)

INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES(‘T Cook’,’Finance’, 40000)
INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES(‘D Michael’,’Finance’, 25000)
INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES(‘A Smith’,’Finance’, 25000)
INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES(‘D Adams’,’Finance’, 15000)

INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES(‘M Williams’,’IT’, 80000)
INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES(‘D Jones’,’IT’, 40000)
INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES(‘J Miller’,’IT’, 50000)
INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES(‘L Lewis’,’IT’, 50000)

INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES(‘A Anderson’,’Back-Office’, 25000)
INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES(‘S Martin’,’Back-Office’, 15000)
INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES(‘J Garcia’,’Back-Office’, 15000)
INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES(‘T Clerk’,’Back-Office’, 10000)

select *,RANK() over(partition by department order by salary desc) as ranking
from @Employees

select *,DENSE_RANK() over(partition by department order by salary desc) as ranking
from @Employees

— Final query

select * from (
select *,RANK() over(partition by department order by salary desc) as ranking
from @Employees
) As T where T.ranking = 2[/code]

Output