Find third highest salary from Employee table

Saturday, July 2, 2011 |


I had discussion regarding few of the TSQL techniques with our .NET developer team and in-between one of the new .NET developer girl, who have just joined the team, told me that she has been asked in few of the interviews regarding this question “Find third highest salary from Employee table” and she had provided solution with big query and calculation.

After listening this, it comes to my mind that we don’t need any big query with calculations, neither we need Rank or row_number etc., it could be achieved with very short and simple query and don’t need any version specific functions like Row_Number, Rank, Dense_rank.

Let us see how we can achieve this:

create table tblEmp
(
ID INT IDENTITY(1,1)
,FirstName varchar(10)
,LastName varchar(10)
,JoiningDate datetime default getdate()
,Salary numeric(10,2)
)
GO

INSERT INTO tblEmp (FirstName,LastName,Salary)
SELECT 'Rushik','Shah',21000 UNION ALL
SELECT 'Prapa','Acharya',21000 UNION ALL
SELECT 'Kalpan','Bhalsod',35000 UNION ALL
SELECT 'Ashish','Patel',18000 UNION ALL
SELECT 'Hetal','Shah',18000
GO

SELECT * FROM tblEmp
GO

--solution given by new .NET developer
declare @maxsal float
set @maxsal = (select  max(salary) from tblEmp
where salary not in (select max(salary) from tblEmp))
select distinct salary from tblEmp
where (salary != @maxsal) and (salary != (select max(salary) from tblEmp))
GO

--this could be easily achieved by Dense_Rank function
Select Salary FROM(
SELECT distinct Salary,dense_rank() over (order by salary desc) as rn FROM tblEmp
) as t where rn=3
GO

--even easy then windows partioning function
--like Dense_Rank
--especially this is not a SQL Server version specific query
SELECT top 1 Salary FROM
(
      select distinct top 3 salary from tblEmp order by Salary
) as t
GO

You can compare all three different queries as performance point of view via execution plan.

Have fun!!!

Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Ask me any SQL Server related question at my “ASK Profile

3 comments:

Anonymous said...

Hi Ritesh,

We can find salary from following query too:


select max(salary) from tblEmp
where salary not in (select top 2 salary from tblEmp order by salary desc )

Thanks & Regards,
Fazal Vahora

Ritesh Shah said...

Hi Fazal,

You are true....

primary key said...

Here is another way to find second highest salary in MySQL and SQ Server 2008