Wednesday, September 07, 2011

SQL Server 2005 + finding 2nd highest salary

Actually have blogged about this few years back here. But recently I ran into an issue with that script. i.e., If there are more than one person getting the same salary then the results weren't coming out properly.

Let me make use of the same table structure as shown in that post.

-- Table Structure
Create table employee
(
[Name] varchar(20),
Sal int
)
Go

-- Populate Sample Records
Insert into employee values ('Vadivel', 80000)
Insert into employee values ('Vel', 70000)
Insert into employee values ('xxx', 40000)
Insert into employee values ('Sneha', 60000)
Insert into employee values ('Amala Paul', 80000) -- Have added this newly

Ideally the result we are expecting is Vel - 70000 as the 2nd highest paid employee. But the below given solution would display 'Amala Paul' as the 2nd highest paid which is wrong.

Select top 1 [Name], Sal from
(
Select Top 2 [Name], Sal from employee order by Sal Desc
) AS A1
Order by Sal

Solution:

In order to handle duplicates I made use of DENSE_RANK() function which is available in SQL Server 2005 and above only.

SELECT [Name], Sal FROM
(
SELECT DENSE_RANK() OVER (ORDER BY Sal DESC) AS Position, [Name], Sal
FROM Employee
) A1
WHERE Position = 2

This query is so generic that you can replace 2 with any number and get that ranked employee details if its available in the table.

No comments: