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
Select 102, 45000
Select 103, 75000
Select 104, 5000
Select 105, 3000
Select 106, 2000
Select 106, 97000

Select * From Salary
Order By Salary Desc

Select Top 1 Salary
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.


No comments: