Second Highest Salary | LeetCode | MSSQL
Tags: leetcode, beginners, tutorial, codenewbie
Published: 2023-06-09 17:20:10

## Problem Statement

The structure of the `Employee` table is as follows:

| Column Name | Type |
| --- | --- |
| id | int |
| salary | int |

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:

| id | salary |
| --- | --- |
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |

Output: 

| SecondHighestSalary |
| --- |
| 200 |

Example 2:

| id | salary |
| --- | --- |
| 1 | 100 |

Output: 

| SecondHighestSalary |
| --- |
| null |

## 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.

```sql
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.

![Runtime Screenshot](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/x4k1y07kgthpl84mk655.png)

### 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.

```sql
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.

![Runtime Screenshot](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/v1d04go2w3pwixwt2f4v.png)

### 3. Using Subquery and MAX Function

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

```sql
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.

![Runtime Screenshot](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ztuewy0dtb2wv0but9dg.png)

## 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](https://leetcode.com/problems/second-highest-salary/description/).

For more solutions and tech-related content, connect with me on my [Beacons page](https://beacons.ai/ranggakd).

{% embed https://beacons.ai/ranggakd %}