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