Find third highest salary from Employee table

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 INTIDENTITY(1,1)
,FirstName varchar(10)
,LastName varchar(10)
,JoiningDate datetime defaultgetdate()
,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 fromtblEmp
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 distincttop 3 salary fromtblEmp order bySalary
) 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
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

2 thoughts on “Find third highest salary from Employee table

  1. Anonymous

    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

Comments are closed.