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

Solution 2: Use MAX() function

Solution 3: Use IFNULL() and window function

• 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;
  
  

All Problems

All Solutions