Second Highest Salary | LeetCode | MSSQL

Problem Statement

The structure of the Employee table is as follows:

Here, the id is the primary key of the table and each row contains salary information for an employee.

Our objective is to write an SQL query to report the second highest salary.

Example 1:

Output:

Example 2:

Output:

Approaches to the Solution

We will explore three approaches, each using different concepts in SQL. We'll explain each method in detail, highlighting their strengths and weaknesses. Additionally, we'll also provide performance results for these solutions.

1. Using Window Function and Subquery

The first solution leverages the window function DENSE_RANK() and a subquery to return the second highest salary.

WITH second_ranking AS (
    SELECT
        Salary,
        DENSE_RANK() OVER (ORDER BY salary DESC) [ranking]
    FROM Employee
), result AS (
    SELECT TOP 1 Salary
    FROM second_ranking
    WHERE ranking = 2
)
SELECT (SELECT Salary FROM result) [SecondHighestSalary]

In the first part of the query, we use DENSE_RANK() to rank salaries in descending order. We then filter out the second highest salary (where ranking = 2) using a subquery. Although this approach is clear and logical, its performance is not optimal, achieving a runtime of 877ms, beating 14.57% of LeetCode submissions.

2. Using Window Function, DISTINCT, and Subquery

The second solution is a variation of the first, with the addition of the DISTINCT keyword to ensure that the selected salary is unique.

WITH second_ranking AS (
    SELECT
        Salary,
        DENSE_RANK() OVER (ORDER BY salary DESC) [ranking]
    FROM Employee
), result AS (
    SELECT DISTINCT Salary
    FROM second_ranking
    WHERE ranking = 2
)
SELECT (SELECT Salary FROM result) [SecondHighestSalary]

The use of DISTINCT adds a uniqueness condition but comes with a slight decrease in performance. This query has a runtime of 984ms, beating 8.46% of LeetCode submissions.

3. Using Subquery and MAX Function

The third solution uses a combination of subqueries and the MAX function to return the second highest salary.

SELECT 
    (
        SELECT MAX(salary) 
        FROM Employee 
        WHERE salary <> (SELECT MAX(salary) FROM Employee)
    ) [SecondHighestSalary]

This solution has a much better performance, achieving a runtime of 649ms, beating 58.23% of LeetCode submissions.

Conclusion

From these three solutions, we learn that in SQL, a more straightforward approach sometimes results in better performance. Using basic SQL functions and operations (like MAX and subqueries) can outperform more complex methods involving window functions. Therefore, the third solution is ranked as the best in terms of overall performance on LeetCode.

However, please note that performance can vary significantly based on the specific RDBMS, data distribution, and table size in real-world applications. So it's important to test different methods in your specific environment.

Find the original problem on LeetCode.

For more solutions and tech-related content, connect with me on my Beacons page.

ranggakd - Link in Bio & Creator Tools | Beacons

@ranggakd | center details summary summary Oh hello there I m a an Programmer AI Tech Writer Data Practitioner Statistics Math Addict Open Source Contributor Quantum Computing Enthusiast details center.

beacons.ai