This portal is to share my acquired knowledge related to Sql Server. I hope will be helpful for novice and professionals.

Sunday, July 18, 2010

Find nth highest salary..

How to find the nth highest salary to salary table??
I have faced many times this question in interviews, I think,
it is the most popular question asked in interview.

Read many articles on Internet and found that following is the best one to find Nth highest salary using TOP clause.

Suppose we want to find 3rd highest salary, then:-

Create Table Salary
(
EmpID Int,
Salary Int
)

Insert Into Salary
Select 101, 25000
UNION
Select 102, 45000
UNION
Select 103, 75000
UNION
Select 104, 5000
UNION
Select 105, 3000
UNION
Select 106, 2000
UNION
Select 106, 97000

Select * From Salary
Order By Salary Desc

--Identify
Select Top 1 Salary
From
(
Select Top 3 Salary
From Salary
Order By Salary Desc
) aa
Order By Salary


OUTPUT : 45000

So simply using this SQL we can find Nth highest salary to change the Second Top clause.

Thanks...

No comments: