Second Highest Salary— Day 75(SQL)

Photo by Alexander Mils on Unsplash

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;

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

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store