Second Highest Salary— Day 75(SQL)

Today for a change we will be solving a SQL problem. Let us look into the code snippet.

176. Second Highest Salary

Write a SQL query to get the second highest salary from the Employee table.

+----+--------+
| Id | Salary |
+----+--------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
+----+--------+

For example, given the above Employee table, the query should return 200 as the second highest salary. If there is no second highest salary, then the query should return null.

+---------------------+
| SecondHighestSalary |
+---------------------+
| 200 |
+---------------------+

One way to solve this problem is to use two queries and get the second max out of it. Let us see the SQL statement.

select MAX(Salary) as SecondHighestSalary from Employee where Salary not in (select MAX(Salary) from Employee);

But this statement does not look practical enough. What if we had to find the 5th max salary? Will we have 5 queries to find the answer?

We need to find something better.

We can sort the salary in descending order and then use offset to offset by 1 and then print the topmost salary.

SELECT IFNULL((SELECT DISTINCT Salary FROM EMPLOYEE
ORDER BY Salary DESC
LIMIT 1 OFFSET 1
), NULL) AS SecondHighestSalary;

More from Annamariya Tharayil

Software Engineer. Find me @ www.linkedin.com/in/annamariya-jt

Love podcasts or audiobooks? Learn on the go with our new app.