Welcome to Subscribe On Youtube

176. Second Highest Salary

Description

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

Solutions

Solution 1: Use Sub Query and LIMIT

We can sort by salary in descending order, and then use the LIMIT statement to get the second highest salary. If the second highest salary does not exist, then return null.

Solution 2: Use MAX() function

We can also use the MAX() function to select the largest salary from the salaries less than MAX().

Solution 3: Use IFNULL() and window function

We can also first calculate the salary ranking of each employee through the DENSE_RANK() function, and then filter out the salary of employees ranked $2$.

• Python
• SQL

• import pandas as pd
  
  
  def second_highest_salary(employee: pd.DataFrame) -> pd.DataFrame:
      # Drop any duplicate salary values to avoid counting duplicates as separate salary ranks
      unique_salaries = employee["salary"].drop_duplicates()
  
      # Sort the unique salaries in descending order and get the second highest salary
      second_highest = (
          unique_salaries.nlargest(2).iloc[-1] if len(unique_salaries) >= 2 else None
      )
  
      # If the second highest salary doesn't exist (e.g., there are fewer than two unique salaries), return None
      if second_highest is None:
          return pd.DataFrame({"SecondHighestSalary": [None]})
  
      # Create a DataFrame with the second highest salary
      result_df = pd.DataFrame({"SecondHighestSalary": [second_highest]})
  
      return result_df
  
  

• # Write your MySQL query statement below
  
  SELECT
      IFNULL(
          (
              SELECT DISTINCT Salary
              FROM Employee
              ORDER BY Salary DESC
              LIMIT 1 OFFSET 1
          ),
          NULL
      )
  AS SecondHighestSalary
  
  
  -- below is ok, but not handling null issue
  SELECT
      (SELECT DISTINCT
              Salary
          FROM
              Employee
          ORDER BY Salary DESC
          LIMIT 1 OFFSET 1) AS SecondHighestSalary;
  
  
  -- Solution 2
  # Write your MySQL query statement below
  SELECT MAX(salary) AS SecondHighestSalary
  FROM Employee
  WHERE salary < (SELECT MAX(salary) FROM Employee);
  
  
  
  -- Solution 3
  # Write your MySQL query statement below
  WITH T AS (SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rk FROM Employee)
  SELECT (SELECT DISTINCT salary FROM T WHERE rk = 2) AS SecondHighestSalary;
  
  

All Problems

All Solutions