Find third highest salary from Employee table

I had discussion regarding few of the TSQL techniques withour .NET developer team and in-between one of the new .NET developer girl, whohave just joined the team, told me that she has been asked in few of theinterviews regarding this question “Find third highest salary from Employeetable” and she had provided solution with big query and calculation.
After listening this, it comes to my mind that we don’t needany big query with calculations, neither we need Rank or row_number etc., itcould be achieved with very short and simple query and don’t need any versionspecific 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)
,JoiningDatedatetime 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 givenby 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 beeasily 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 thenwindows partioning function
–likeDense_Rank
–especiallythis 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 performancepoint of view via execution plan.
Have fun!!!
Reference: Ritesh Shah

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

4 Responses to “Find third highest salary from Employee table”

  1. Anonymous Says:

    Hi Ritesh,We can find salary from following query too:select max(salary) from tblEmpwhere salary not in (select top 2 salary from tblEmp order by salary desc )Thanks & Regards,Fazal Vahora

  2. Anonymous Says:

    Hi Ritesh,We can find salary from following query too:select max(salary) from tblEmpwhere salary not in (select top 2 salary from tblEmp order by salary desc )Thanks & Regards,Fazal Vahora

  3. Ritesh Shah Says:

    Hi Fazal,You are true….

  4. Ritesh Shah Says:

    Hi Fazal,You are true….


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: