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 fromEmployee

)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 fromEmployee order byBasic_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 fromEmployee

where 2=(select COUNT(*) from Employee E whereE.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 fromEmployee

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

**So formula for nth highest value will be:-**

select Ename,Basic_Sal fromEmployee

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

**where N= nth highest value to find out.**

I hope you will find this article helpful.

2,835 total views, 2 views today

#

Sir please elaborate little bit

I am unable to understand this

select Ename,Basic_Sal from Employee

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

#

Sir understood this by executing the following query

select * from Employee E, Employee where E.Basic_Sal >Employee.Basic_Sal

#

Hello friend.Sorry for delayed response.It really good to know that you understood the query.I just want to let you know that I am not a full time blogger but I am quite regular on blogger.Please be informed whenever you ask any query ,please wait until weekends.one more thing,please mention your name as well.thanks for reading my blog…