How to find nth highest value of a column in SQL Server / How to find nth highest Salary

Hello friends, today I am going to explain how we can find out nth highest value from a column in SQL server. This is one of the popular question in any SQL interview. I am going to write down different queries to find out the nth highest value of a column.

 I have created a table named “Emloyee” by running the below script.

CREATE TABLE Employee([Eid] [int] NULL,[Ename] [varchar](255) NULL,[Basic_Sal] [float] NULL)

Now I am going to insert 8 rows into this table by running below insert statement.

insert into Employee values(1,'Neeraj',45000)

insert into Employee values(2,'Ankit',5000)

insert into Employee values(3,'Akshay',6000)

insert into Employee values(4,'Ramesh',7600)

insert into Employee values(5,'Vikas',4000)

insert into Employee values(7,'Neha',8500)

insert into Employee values(8,'Shivika',4500)

insert into Employee values(9,'Tarun',9500)

Now we will find out 3rd highest Basic_sal from the above table using different queries.

I have run the below query in management studio and below is the result.

select * from Employee order by Basic_Sal desc

We can see in the above image that 3^rd highest Basic Salary would be 8500. I am writing 3 different ways of doing the same. By running all three mentioned below queries we will get same result i.e. 8500.

First Way: - Using row number function

select Ename,Basic_sal

from(

            select Ename,Basic_Sal,ROW_NUMBER() over (order by Basic_Sal desc) as rowid from Employee

      )A

where rowid=2

To Find Nth highest Value:-

select Ename,Basic_sal

from(

            select Ename,Basic_Sal,ROW_NUMBER() over (order by Basic_Sal desc) as rowid from Employee

      )A

where rowid=N

Second Way: - Using Top Clause

select top 1 Ename,Basic_Sal from(select top 3 Ename,Basic_Sal from Employee order by Basic_Sal desc)A order by  Basic_Sal

To Find Nth highest Value:-

select top 1 Ename,Basic_Sal from(select top N Ename,Basic_Sal from Employee order by Basic_Sal desc)A order by  Basic_Sal

Third Way: - Using Correlated query

select Ename,Basic_Sal from Employee

where 2=(select COUNT(*) from Employee E where E.Basic_Sal >Employee.Basic_Sal)

In third query if we want to get 4th highest Basic Salary then query will be modified like below.

select Ename,Basic_Sal from Employee

where 3=(select COUNT(*) from Employee E where E.Basic_Sal >Employee.Basic_Sal)

So formula for nth highest value will be:-

select Ename,Basic_Sal from Employee

where N-1=(select COUNT(*) from Employee E where E.Basic_Sal >Employee.Basic_Sal)

where N= nth highest value to find out.

I hope you will find this article helpful.