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