Hello Friends,

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

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

Output

Comments

Leave a comment

Tell us about yourself

Your Name				(required field)
Your Email Address				(required field)
Website URL

Comment and preferences

Your Comment
		Subscribe to this comment thread Please enter the text you see in the picture: Submit