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 3rd 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.