176. Second Highest Salary

Second Highest Salary

Problem Description

SQL Schema

    

     Create table If Not Exists Employee (id int, salary int)

    Truncate table Employee

    insert into Employee (id, salary) values ('1', '100')

    insert into Employee (id, salary) values ('2', '200')

    insert into Employee (id, salary) values ('3', '300')

Table: Employee

+-------------+------+
| Column Name | Type |
+-------------+------+
| id          | int  |
| salary      | int  |
+-------------+------+
id is the primary key column for this table.
Each row of this table contains information about the salary of an employee.

 

Write an SQL query to report the second-highest salary from the Employee table. If there is no second highest salary, the query should report null.

The query result format is in the following example.

 

Example 1:

Input: 
Employee table:
+----+--------+
| id | salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+
Output: 
+---------------------+
| SecondHighestSalary |
+---------------------+
| 200                 |
+---------------------+

Example 2:

Input: 
Employee table:
+----+--------+
| id | salary |
+----+--------+
| 1  | 100    |
+----+--------+
Output: 
+---------------------+
| SecondHighestSalary |
+---------------------+
| null                |
+---------------------+

Idea:

Approach 1)

• Use limit, order by, if the null combination

# Write your MySQL query statement below
select IFNULL(
(select distinct
    Salary
from
    Employee
order by
    Salary desc
limit
    1, 1), NULL) SecondHighestSalary;

Approach 2)

• Two queries, first find the maximum value, and then find the second largest value through not in.

select
    max(Salary) as SecondHighestSalary 
from
    Employee
where
    Salary
not in(
    select max(Salary) from Employee
)

Approach 3) Two brushes

• Filter out the second largest number by the subquery.
• Judging by IFNULL, if it does not exist, fill in NULL.
• Use DISTINCT to deduplicate.
• How to use IFNULL
• select IFNULL(expression1, expression2) newname;
• If expression1 is null, then expression1 is displayed and the field name is newname.

SELECT IFNULL(
(SELECT distinct Salary
FROM Employee
ORDER BY Salary desc limit 1,1)
, null) as SecondHighestSalary ;

Conclusion

That’s all folks! In this post, we solved LeetCode problem #176. Second Highest Salary using 3 approaches.

I hope you have enjoyed this post. Feel free to share your thoughts on this.

You can find the complete source code on my GitHub repository. If you like what you learn. feel free to fork 🔪 and star ⭐ it.

In this blog, I have tried to solve leetcode questions & present the most important points to consider when improving Data structure and logic,  feel free to add, edit, comment, or ask. For more information please reach me here
Happy coding!