Aspdotnet-Suresh

aspdotnet-suresh offers C#.net articles and tutorials,csharp dot net,asp.net articles and tutorials,VB.NET Articles,Gridview articles,code examples of asp.net 2.0 /3.5,AJAX,SQL Server Articles,examples of .net technologies

Query to Find Nth Highest Salary of Employee In Sql Server | Query to Find 2nd or 3rd highest salary of employee in SQL Server

Dec 10, 2011
Introduction:

In this article I will explain how to find nth highest salary of the employee in SQL Server.

Description:

In many sites we will find one frequent question that is like how to find 2nd highest salary of employee or how to find 3rd highest salary of employee or how to find nth highest salary of employee in SQL Server. To get required highest salary we are having different alternative ways.

Before write queries to get 2nd, 3rd or nth highest salary of employee first design table in database and give name as “EmployeeDetails

EmpID
EmpName
Salary
1
Suresh
7000
2
Prasanthi
8000
3
Mahesh
9000
4
Sai
10000
5
Nagaraju
11000
6
Mahendra
12000
7
Sanjay
13000
8
Santhosh
14000
9
Raju
15000
10
Phani
10000
11
Kumar
12000
12
Prasad
9000
13
Siva
12000
14
Madhav
14000
15
Donthi
11000

Once table design completed now we will see different ways to get 2nd, 3rd, etc or nth highest salary of employee.

Get Highest Salary from Employee Table

Use the below query to get 1st, 2nd, 3rd, 4th, 5th ….etc highest salary of employee

SELECT MAX(Salary) as 'Salary' from EmployeeDetails
where Salary NOT IN
(
SELECT TOP n-1 (SALARY) from EmployeeDetails ORDER BY Salary Desc
)
Here in above query we need to replace the “n” value with required highest salary of employee

To get 2nd highest salary of employee then we need replace “n” with 2 our query like will be this

SELECT MAX(Salary) as 'Salary' from EmployeeDetails
where Salary NOT IN
(
SELECT TOP 1 (SALARY) from EmployeeDetails ORDER BY Salary Desc
)
Output will be like this

 Salary
14000

To get 3rd highest salary of employee then we need replace “n” with 3 our query like will be this

SELECT MAX(Salary) as 'Salary' from EmployeeDetails
where Salary NOT IN
(
SELECT TOP 2 (SALARY) from EmployeeDetails ORDER BY Salary Desc
)
Output will be like this

 Salary
13000

The above query will help us to get highest salary from EmployeeDetails table suppose if we want to get Employee Details with particular highest salary then we need to use below query to get required details.

Get Employee Details with Particular Highest salary

Use the below query to get Employee Details with 1st, 2nd, 3rd, 4th, 5th ….etc highest salary from table

SELECT *
FROM EmployeeDetails e1
WHERE (n-1) = (
SELECT COUNT(DISTINCT(e2.Salary))
FROM EmployeeDetails e2
WHERE e2.Salary > e1.Salary)
Here in above query we need to replace the “n” value with required highest salary of table

To get 2nd highest salary of Employee Details then we need replace “n” with 2 our query like will be this

SELECT *
FROM EmployeeDetails e1
WHERE (1) = (
SELECT COUNT(DISTINCT(e2.Salary))
FROM EmployeeDetails e2
WHERE e2.Salary > e1.Salary)
Output will be like this

EmpID
EmpName
Salary
8
Santhosh
14000
14
Madhav
14000

To get 3rd highest salary of employee then we need replace “n” with 3 our query like will be this

SELECT *
FROM EmployeeDetails e1
WHERE (2) = (
SELECT COUNT(DISTINCT(e2.Salary))
FROM EmployeeDetails e2
WHERE e2.Salary > e1.Salary)
Output will be like this

EmpID
EmpName
Salary
7
Sanjay
13000

In this way we can get required employee details with particular highest salary.

If you enjoyed this post, please support the blog below. It's FREE!

Get the latest Asp.net, C#.net, VB.NET, jQuery, Plugins & Code Snippets for FREE by subscribing to our Facebook, Twitter, RSS feed, or by email.

subscribe by rss Subscribe by RSS subscribe by email Subscribe by Email

30 comments :

viresh rajput said...

very nice post dear...........keep it up great job.

Anonymous said...

The sqlquery is very confused and difficult in examples

Anonymous said...

very difficult examples...

Unknown said...

HI
SIR..
VERY GOOOD ANSWERS...

Anonymous said...

Thanks...

Anudeep said...

IAm NOt getting Sorry

zaidzaidi said...

Hi Suresh Sir

myself Zaid Abbas Zaidi
its a Good one Article about finding nth Higest Salary..
if am not wrong we can also do the same thing by using aggreate function like Min or Max..

Like if i want to know Nth Highest Salary then i can

SELECT MIN(salary) FROM emp
WHERE salary IN
(SELECT TOP N salary FROM emp ORDER BY salary DESC);

dinesh.net said...

very nice suresh garu but i have one doubt
Ex: consider ur table ,
now i would like to display total number of employees count who having 8000,9000and 14000 salry and same for all salaries

Tokcy tech said...

nice..

Krishan Kumar said...

very nice toping and usefull for me to get easiest way to get height value of any position.

krishan
Thanks

Unknown said...

Good One. Thanks.
But can some one explain me the logic, how it is working internally to produce output?

Amit.

Himanshu Pandey said...

Hi suresh ,
i was going through your post and i have some doubts.i think its wrong.
like i was trying this query.
in my db i have records -:
a 1000
b 2000
c 3000
d 5000
e 6000
f 8000
i 9000
j 10000
k 12000
l 14000
m 13000
n 16000
n 17000
when i am running your query its giving me result 6000,where it is supposed to return 14000,the 3rd highest salary???pl help

Manjunathan Mani said...

Find the person who is having second largest salary from Employee(Empname,salary)table can u any one help me.

Manjunathan Mani said...

HI All i given a input like India but i need out put like it i, How i am going to write a c program?
Can u any one help me pls

jassu said...

Superb, thanks lotzzzzzzzzz.

bhavana said...

this is ok but this query is too big make it sort and write for better user

Anonymous said...

Its Very nice and useful

Anonymous said...

Anonymous said...

aaaddesffesffessf

Rajan Singh said...

Plz help me in this question!!!

How to determine the lowest and highest salary by employee details in employee table

Anonymous said...

Suresh sir,if i have any doubt i browse for your articles....Thanks

Shyamala said...

Hi Suresh.. It will be better to mention 'How it Works' while giving query solutions.. it will be helpful for lower level programmers understanding..

Anonymous said...

Thank u so much..it really helped me

Anonymous said...

Here are couple of more ways to find second highest salary in SQL

Anonymous said...

its very usefull...

Anonymous said...

Hi all,
To find 3rd highest salary we can also use this SQL:
Select max(salary) from
(select distinct salary from employee order by salary desc)
Where rownum < 4;

You can change the number as per n+ 1. Where n is number.

Amit Singh said...

Ur query will give wrong value if try to find 3rd or 4th highest salary because ur list has duplicate value (salary)

to obtain correct result use DISTINCT keyword just before top

try it

Your Post is also heipful,Thanks for it
Amit

BINDU KUMARI said...

SELECT *
FROM EmployeeDetails e1
WHERE (1) = (
SELECT COUNT(DISTINCT(e2.Salary))
FROM EmployeeDetails e2
WHERE e2.Salary > e1.Salary)

THIS WILL GIVE THE SECOND HIGHEST SALARY NOT A 1ST BECAUSE OF WE GAVE HERE WHERE (1) , IF WILL GIVE WHERE (0) THEN EAT WILL DISPLAY 1ST HIGHEST SALARY

hrushikesh behera said...

select * from emp where sal=(select MAX(sal) as ghy from emp where sal not in(select top 4 sal from emp order by sal desc))

vijay bhatt said...

/*for 3rd highest salary n=2, for 2nd highest salary n=1, nth highest salary n=n-1*/
SELECT DISTINCT(Salary) FROM employee ORDER BY Salary DESC LIMIT n,1

Give your Valuable Comments

Other Related Posts

© 2010-2012 Aspdotnet-Suresh.com. All Rights Reserved.
The content is copyrighted to Suresh Dasari and may not be reproduced on other websites without permission from the owner.