176. Second Highest Salary - Detailed Explanation

Problem Statement

You are given an Employee table with the following schema:

• Id (integer): The unique employee ID.
• Salary (integer): The salary of the employee.

Write a SQL query to get the second highest salary from the Employee table. If there is no second highest salary (for example, when there is only one distinct salary in the table), the query should return null.

Example Inputs & Outputs

• Example 1:

  • Employee Table:

    +----+--------+
    | Id | Salary |
    +----+--------+
    | 1  | 300    |
    | 2  | 200    |
    | 3  | 100    |
    +----+--------+
    

  • Output: 200
  • Explanation:
    The highest salary is 300, and the second highest distinct salary is 200.

• Example 2:

  • Employee Table:

    +----+--------+
    | Id | Salary |
    +----+--------+
    | 1  | 100    |
    +----+--------+
    

  • Output: null
  • Explanation:
    There is only one salary value in the table; hence, there is no second highest salary.

• Example 3:

  • Employee Table:

    +----+--------+
    | Id | Salary |
    +----+--------+
    | 1  | 400    |
    | 2  | 400    |
    | 3  | 300    |
    +----+--------+
    

  • Output: 300
  • Explanation:
    Although there are multiple rows with the highest salary (400), the next distinct salary is 300, which is the second highest.

Constraints

• The Employee table can have one or more rows.
• Salaries are positive integers.
• There might be duplicate salary values.

Hints

1. Using Subqueries:
   Think about first finding the highest salary, then using it to filter out those records when searching for the next highest.

2. Handling Duplicates:
   Since the table can contain duplicate salaries, ensure that you consider only distinct salary values when determining the second highest.

Approaches

Approach 1: Using a Subquery

Idea:

• First, determine the highest salary using a subquery.
• Then, select the maximum salary that is less than this highest salary.

SQL Query:

SELECT 
    (SELECT MAX(Salary) 
     FROM Employee 
     WHERE Salary < (SELECT MAX(Salary) FROM Employee)
    ) AS SecondHighestSalary;

Explanation:

• The inner subquery (SELECT MAX(Salary) FROM Employee) finds the highest salary.
• The outer query then finds the maximum salary that is less than that value.
• If there is no such salary (i.e., if the table contains only one distinct salary), the result will be null.

Approach 2: Using LIMIT/OFFSET (MySQL Specific)

Idea:

• Use DISTINCT to filter duplicate salary values.
• Order the distinct salaries in descending order.
• Use LIMIT with an offset to get the second element.

SQL Query:

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

Explanation:

• SELECT DISTINCT Salary FROM Employee gets unique salary values.
• ORDER BY Salary DESC sorts them in descending order.
• LIMIT 1 OFFSET 1 skips the highest salary and returns the next one.
• This query returns null if there is no second highest salary.

Simulated Implementations

Since the original problem is SQL-based, here are simulated implementations in Python and Java that mimic the logic of retrieving the second highest salary from an in-memory dataset.

Python Code