Top 2nd Highest Salary From a Table in SQL

Top 2nd Highest Salary From a Table in SQL

In many Interview I often see Interviewers asking this questionhow to calculate top 2nd highest salary or top 3rd highest Salary or n salary...

So I thought to have a Blog on this.

For this we will create a table Employee

create table Employee

(Empid int identity (1,1) ,EmpName nvarchar(50),EmpSalary float)

insert into Employee values('Amit','5000'),('Sumit','6000'),('Raj','8000'),('vijay','9000'),('suresh','10000')

select * from Employee

Output

Empid EmpName EmpSalary

1 Amit         5000

2 Sumit 6000

3 Raj        8000

4 vijay        9000

5 suresh     10000

Now Suppose we want 2nd Highest Salary which is 9000 for this write

select min(empsalary) as salary from

 (select top 2 empsalary from employee order by empsalary desc)D

The Explanation is like the inner query will give 2 max salary order by desc

10,000

9000

The Outer query will take the minimum value from inner query which we have kept in Derived table D which is 9000

Now your Interview ask he want 3 rd  highest Salary For that simply change the Inner query to TOP 3

10000

9000

8000

The Outer query will take the minimum salary which is 8000

So its quite simple just by modifying the inner query you can have any no. of Highest salary from a Table

Note: There are other ways also to achieve this result but i find this most simple and Easy.