# How to find second highest salary in SQL step by step?

In my previous articles I have given the way to write the complex sql queries. In this article I would like to focus on multiple ways to find second highest salary of employee in SQL. This is most asked interview question in SQL related interviews. I would like to throw light on multiple queries with its stepwise explanation.

### Query to Find second highest Salary Of Employee one of the most commonly asked question in SQL interviews:

select distinct salary from Empoyee e1 where 2 = (select count(distinct salary) from Employee e2 where  e1.salary <= e2.salary);

#### Explanation of Query for example :

1.Distinct keyword:–>The Distinct keyword is very useful keyword which will eliminate duplicates and fetches the Result.

2.Aliases–> Alias is nothing but the obfuscation technique to protect the real names of database fields

3.Count()–>Count() function is used to count the records in the table.

Step 1: So to understand the above query we will start with simple select statement. for Example,

Select * from Employee;

Simultaneously you can see example of select statement here

Output:

 Employee_num Employee_name Department Salary 1 Amit OBIEE 680000 2 Rohan OBIEE 550000 3 Rohit OBIEE 430000

Here our intention is to fetch the record where salary is 2nd highest.

Step 2 :We will fetch the distinct Salary of employee and give the alias to it. For instance ,

Select distinct Salary from Employee e1;

Output:

 Salary 680000 550000 430000

Step 3 :We need to calculate 2nd highest salary.So we need to get the count of all distinct salaries. To illustrate this ,

Select count(distinct Salary) from Employee e2;

Output:

3

Step 4 :We actually need to calculate 2nd highest salary.So we will modify the Step 2 query and Step3 Query: Below is example,

Select distinct Salary from Employee e1 where 2=

Select count(distinct Salary) from Employee e2 where e1.salary<=e2.salary;

Output:

 550000

The above query will give us the 2nd highest salary of employee. Hope you will understand the logic behind fetching the second highest salary. There are other ways to find 2nd highest salary of employee using rank and dense_rank function.

## Other ways to find 2nd highest salary:

Query 1:

SELECT max(e1.sal), e1.deptno FROM s_emp e1 WHERE sal < (SELECT max(sal) FROM s_emp e2 WHERE e2.deptno = e1.deptno) GROUP BY e1.deptno;

Query 2:

SELECT * FROM (SELECT S.*,DENSE_RANK() OVER (PARTITION BY DNO ORDER BY SALARY DESC) DR FROM SOURCE ) S WHERE S.DR=2;

Query 3:

Select Name From Employees Where Salary =

(Select Distinct Top(1) Salary from Employees where Salary Not In

(Select Dustinct Top(1) Salary from Employees Order By Salary Descending) Order by Salary Descending);

There are some other ways of calculating the second highest salary in different DBMS i.e. Oracle,Mysql,Postgresql:

Type 1 : Using correlated subquery:

SELECT name, salary FROM Employee e1 WHERE N-1 = (SELECT COUNT(DISTINCT salary) FROM Employee e2 WHERE e2.salary > e1.salary)SELECT name, salary FROM Employee e1 WHERE 2-1 = (SELECT COUNT(DISTINCT salary) FROM #Employee e2 WHERE e2.salary > e1.salary);

Using this method of finding out the second highest salary is slow because of the execution of inner query again and again.

Type 2: Microsoft SQL Server (Using Top Function)

SELECT TOP 1 salary FROM ( SELECT DISTINCT TOP N salary FROM Employee ORDER BY salary DESC ) AS temp ORDER BY salary;

Type3 : Mysql (using limit Clause)

SELECT salary FROM Employee ORDER BY salary DESC LIMIT 1,1;

Type 4: Oracle Function (using rownum function)

SELECT Salary FROM ( SELECT e.Salary, ROW_NUMBER() OVER (ORDER BY salary DESC) rn FROM Employee e ) WHERE rn = 2;

Type 5 : One of the website reader has suggested this solution Solution of Gautam Roy :

select * from OrderDetails a where a.Quantity < (select max(b.Quantity) from OrderDetails b) order by a.Quantity DESC limit 1;

Type 6 :However  another Reader as given the below as to illustrate,

name       sal
———-  ———————
x             120.00
y             140.00
z             200.00
p            400.00

select max(sal) as maxsalary from salary where sal < ( select max(sal) as maxsalary from salary );

Output :

maxsalary

——————

200.00

You can try this also for getting results easy accordingly,

Type 7 : Suggested by one of the Reader Selva Saleen for example :

select salary from (select rownum row_num,salary from (select * from EMPLOYEE E order by E.salary desc))where row_num=2;

Type 8 : Find Nth Highest Salary using Limit Keyword Suggested by Reader named Dinesh Singh for example  :

Select name, salary from employee order by salary desc limit(1,n-1);

Type 9 :The next  New Solution  suggested by reader named Victor for example :

select distinct e2.salary
from (select A.Employee_num , A.salary , count(distinct B.salary) as salary_distinct_rank
from Employee A
left join
Employ B
where B.salary <= A.salary
group by A.Employee_num, A.salary ) e2
where e2.salary_distinct_rank = 2 ;

Type 10 : New Solution suggested by reader named Nikhita:

Select TOP 1 Salary from Employee where Salary<(Select max(Salary) from Employee);

These are above methods to calculate 2nd highest salary of Employee.If you like this article then don’t forget to share it.

I hope you like this article and the article is useful to find second highest salary in multiple ways. If you like this article or if you have any issues with the same kindly comment in comments section.

## 38 Replies to “How to find second highest salary in SQL step by step?”

1. Stevan says:

If I may first compliment the site, it is helping me tremendously with my studies.

My Question is regarding a very small part of this explanation for your consideration the original solution : “select distinct salary from Empoyee e1 where 2 = (select count(distinct salary) from Employee e2 where e1.salary <= e2.salary);"

what is the purpose/function of the "2" in the "where 2"?

Sorry for the basic question I have not come across a source that has covered this particular use yet.

1. You need to fragament the query in to different sections.

Step 1 : select distinct salary from Empoyee e1 where 2 =

The answer of this query is distinct salary of Employee

Step 2 : Query 2
select count(distinct salary) from Employee e2 where e1.salary <= e2.salary) Here we are taking count of distinct salary Step 3 : We want second highest salary...so we are taking 2 as a constant number..if you want 3rd higest salary you may take 3. Hope you got answer..

2. Abhishek Simla says:

Hi Stevan,

select distinct salary from Empoyee e1 where 2 = (select count(distinct salary) from Employee e2 where e1.salary = a given salary. Here for each row of the outer query the given salary to the inner query changes. When outer query selects some row which holds the 2nd highest salary, the resulting inner query answer is 2, because there can only be 2 salaries >= 2nd highest salary.
That is why the where clause is formed as “2 = “, so that when inner query results in 2, that is our answer.
That “2 =” in itself doesn’t do much. It is the inner query which actually calculates the answer.

I hope that clarifies your doubt.

1. Thanks Abhishek..This will work !!

2. Selva Seelan says:

Please give suggestion for this query using rownum :

select salary from (select rownum row_num,salary from (select * from EMPLOYEE E order by E.salary desc))where row_num=2;

1. Hi Selva,

This query is also working..If you want me to add in article with your name as credits do let me know..

1. Selvas Seelan says:

1. I have added it.. 🙂 Thanks for suggestions..

3. Akansha says:

Hi Amit,

I think type 4 query needs to be validate, it should be
select sal from
(select (e.sal),row_number() over (order by sal desc) rn from emp e group by e.sal)
where rn=3

1. Hi Akanksha,

The query which you have given is calculating the third highest salary of employee.

The query given in article is abs correct. I have checked it.

4. Eyal says:

I didn’t understand this part – ‘where e1.salary <= e2.salary'. Why to use this?

1. This condition is to compare 2 salaries to find out the second highest salary.

5. Muthsamy says:

select max(salary) from employees where salary not in (select max(salary) from employees)

1. Thank u very much Muthusamy..

6. Hawwa fathima says:

select distinct salary from Empoyee e1 where 2 = (select count(distinct salary) from Employee e2 where e2.salary <= e1.salary);

Why it has displaying 2nd least salary

1. Hi,

It is not giving second least salary..The query will give you second highest salary for employee. Kindly check!!!

7. Ketan says:

Hi ,what about below query ..
Select salary from employee where salary in (select distinct salary from employee order by salary desc ) and rownum =2 ;

1. Thanks Ketan!!

Above query will give us second highst salary of Employee!!!

Regards,
Amit

2. Debashis Mohapatra says:

Yes..it is much easiler..

8. DINESH SINGH says:

Select name, salary from employee order by salary desc limit(1,n-1);

1. Thanks…This query will work where N is number of highest salary which you want to display.

9. Ips says:

Sorry but I am still confused about the 2 in where clause? Is it a count number for comparing the second highest salary in the inline query?Thanks in advance!

10. Mohamed Dahab says:

it is so easy….
SELECT salary FROM employee ORDER BY salary DESC LIMIT 1, 1;

11. victor says:

I would have been more verbose

select distinct e2.salary
from (select A.Employee_num , A.salary , count(distinct B.salary) as salary_distinct_rank
from Employee A
left join
Employ B
where B.salary <= A.salary
group by A.Employee_num, A.salary ) e2
where e2.salary_distinct_rank = 2 ;

12. Nikitha says:

Can we use this to find the second highest salary
Select TOP 1 Salary from Employee where Salary<(Select max(Salary) from Employee)

1. Yes Nikitha..This query is also working. I am adding this with your name in my article.

13. Suma Sreeraman says:

This is another way to find the solution

Select top 1 * from Employees
where salary NOT IN (Select Max(salary) from Employees)
order by salary desc

1. Hello Suma,

This is also working. I will add it in to article with your name.

Thanks for suggestions!!

Regards and thanks,
Amit S

14. Partha Roy says:

SELECT MAX(Salary) FROM Employee<SELECT MAX(Salary) FROM Employee;

1. Thanks partha for your solution!!

15. rajat says:

Write a query to display the name(s) of employee(s) who draw(s) the highest salary. In case of multiple records, display the records sorted in ascending order based on employee name.

1. You can use filter condition as 1 in the specified query of this article.

16. Caio says:

Hey, I think this query also works fine:

SELECT *
FROM EMPLOYEE
ORDER BY SALARY DESC
OFFSET 1 ROW
FETCH NEXT 1 ROW ONLY

17. Gauri Sawant says:

Hi Amit,
Solution in Type 7 is not working , as rownum = constant this condition will be satisfied only for rownum = 1 ,
using rownum I think below query will work, please verify.
select min(sal) from (select * from emp order by sal desc) where rownum <= 2