
Interview Question: What is the Query to Find Second Highest Salary of Employee?
Query to find Second highest salary of employee is one of the most commonly asked question in SQL interviews:
Answer:
select distinct salary from Empoyee e1 where 2 = (select count(distinct salary) from Employee e2 where e1.salary <= e2.salary);
CLICK HERE TO GET 20 COMPLEX SQL QUERIES IMPORTANT FOR INTERVIEW
 Explaination of Query:
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.
Select * from Employee;
CLICK HERE TO GET STEP BY STEP EXECUTION OF SELECT STATEMENT
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 second highest.
Step 2 :We will fetch the distinct Salary of employee and give the alias to it.
Select distinct Salary from Employee e1;
Output:
Salary 
680000 
550000 
430000 
Step 3 :We need to calculate Second highest salary.So we need to get the count of all distinct salaries.
Select count(distinct Salary) from Employee e2;
Output:
3
Step 4 :We actually need to calculate second highest salary.So we will modify the Step 2 query and Step3 Query:
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 second highest salary of employee.Hope you will understand the logic behind fetching the second highest salary.There are other ways to find second highest salary of employee using rank and dense_rank function.
Other ways to find second 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 DISTINCT 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 N1 = (SELECT COUNT(DISTINCT salary) FROM Employee e2 WHERE e2.salary > e1.salary)SELECT name, salary FROM Employee e1 WHERE 21 = (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 : Another Reader suggested solution of Bharath chary Vadla
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
Type 7 : Suggested by one of the Reader Selva Saleen :
select salary from (select rownum row_num,salary from (select * from EMPLOYEE E order by E.salary desc))where row_num=2;
These are above methods to calculate 2nd highest salary of Employee.If you like this article then don’t forget to share it.
CLICK HERE TO GET INFORMATION ABOUT TECH MAHINDRA INTERVIEW QUESTIONS
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.
Thank you for your time to answer this basic notion in advance
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..
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;
Hi Selva,
This query is also working..If you want me to add in article with your name as credits do let me know..
Thank you!!. Please add the query only. it will do. 🙂
I have added it.. 🙂 Thanks for suggestions..
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
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.