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 SQL query to find the second highest distinct salary from the Employee table. If there is no second highest salary, return null.

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

-- Approach: Using subquery and LIMIT clause
SELECT
    (SELECT DISTINCT
            salary
        FROM
            Employee
        ORDER BY
            salary DESC
        LIMIT 1 OFFSET 1) AS SecondHighestSalary;

Explanation:

1. DISTINCT salary: This ensures that we only consider unique salary values.
2. ORDER BY salary DESC: This sorts the salaries in descending order, so the highest salary comes first.
3. LIMIT 1 OFFSET 1:
   • LIMIT 1 restricts the result to a single row.
   • OFFSET 1 skips the first row (which is the highest salary), effectively selecting the second highest.
4. Outer SELECT statement: If the subquery returns no rows (i.e., there's no second highest salary), the outer query returns NULL. This handles the case where there is only one distinct salary or no salaries at all.

Alternative Approach (Using DENSE_RANK() Window Function):

SELECT
    (SELECT DISTINCT salary
     FROM (
         SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) as rank
         FROM Employee
     ) AS ranked_salaries
     WHERE rank = 2) AS SecondHighestSalary;

Explanation for the alternative approach:

1. DENSE_RANK() OVER (ORDER BY salary DESC): This assigns a rank to each distinct salary based on its value. The highest salary gets rank 1, the second highest gets rank 2, and so on. DENSE_RANK() ensures that consecutive ranks are assigned even if there are duplicate salaries.
2. Inner Subquery (ranked_salaries): This subquery calculates the rank for each salary.
3. Outer Subquery: This selects the salary with rank 2 from the results of the inner subquery.
4. Outer SELECT Statement: Similar to the first approach, if no salary has rank 2 (e.g., only one distinct salary exists), the query returns NULL.

Edge Cases

• Empty Table: If the Employee table is empty, both queries will return NULL, which is the desired behavior.
• Single Distinct Salary: If the table contains only one distinct salary (e.g., all employees have the same salary), both queries will return NULL.
• Duplicate Salaries: The DISTINCT keyword ensures that duplicate salary values are treated as one. Both queries correctly handle cases with duplicate salaries and return the second highest distinct salary.
• Negative Salaries: If the salary column contains negative values, the queries will still work correctly, as the ORDER BY clause sorts the salaries based on their numerical value, regardless of sign.