176 Second Highest Salary

Description

Table: Employee

• id is the primary key column for this table.
• Each row of this table contains information about the salary of an employee.

Problem Statement

Write a SQL query to report the second highest salary from the Employee table. If there is no second highest salary, the query should report null.

The query result format is in the following example.

Example 1:

Input:

• Employee table:

Output:

Example 2:

Input:

• Employee table:

Output:

Solution

Here, we need second highest salary. This could be solved in multiple ways.

Approach 1: Using LIMIT and OFFSET

You could order the results by salary and you need only the second value so you could write LIMIT 1 OFFSET 1. However, you also need to take care of the edge case that if there is no second highest value, you should return NULL. You could use IFNULL function in SQL which checks first value and if it is NULL then it returns the second value.

1SELECT IFNULL(
2        (SELECT DISTINCT(Salary) FROM Employee
3            ORDER BY Salary DESC
4            LIMIT 1
5            OFFSET 1),
6        NULL
7    ) SecondHighestSalary;

Approach 2: Using Window Functions

This is little complicated than the previous one and usually less readable.

In this case, because you need second highest salary, you could sort the records by Salary and retrieve the second value. This could be done by creating a window which is not partitioned by any column but ordered by Salary in descending order. Now, if two employees have same salary, it should return same rank, so that you can retrieve the second highest salary. This can be done using DENSE_RANK window function. If you accidentally use RANK function, it may not result in correct result if we have two employees with same salary. For example,

Check the output of below query.

1WITH cte AS (
2    SELECT Salary,
3        DENSE_RANK() OVER (ORDER BY Salary DESC) dense_rank
4        FROM Employee
5) SELECT *
6FROM cte;

With this, you can see that you can retrieve the second highest salary by querying rnk=2. One issue is that if there is no record with rnk = 2, you will end up getting no rows as output. This is where you have to check that you’ve at least one record with rnk = 2 and then return the value of Salary. On the other hand if there is no row, you return NULL.

1WITH cte AS (
2    SELECT Salary,
3        DENSE_RANK() OVER (ORDER BY Salary DESC) rnk
4        FROM Employee
5) SELECT IF (COUNT(1) != 0, Salary, NULL) SecondHighestSalary
6FROM cte
7WHERE rnk = 2;