LeetCode //MySQL - 176. Second Highest Salary

176. Second Highest Salary

Table: Employee

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

Write a solution to find the second highest salary from the Employee table. If there is no second highest salary, return null (return None in Pandas).

The 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 |
±--------------------+

From: LeetCode
Link: 176. Second Highest Salary

---

Solution:

Ideas:

To achieve this, we can use the DISTINCT keyword to get unique salaries and then use the LIMIT clause along with OFFSET to get the second highest salary. If there is no second highest salary, we should return NULL.

Code:

SELECT 
    (SELECT DISTINCT salary 
     FROM Employee 
     ORDER BY salary DESC 
     LIMIT 1 OFFSET 1) AS SecondHighestSalary;