[TOC] ## Solution --- [Pandas](#pandas) [Database](#database) # pandas ### Approach 1: Sorting #### Algorithm Given the table `employee` as follows:

id	salary
1	100
2	200
3	300
4	200

We first drop any duplicate salaries.

id	salary
1	100
2	200
3	300

Then we handle the case if there are less than two unique salaries. If there are less than two unique salaries, then we return `np.NaN` as the second highest salary. Otherwise, we sort the table by salary in descending order.

id	salary
3	300
2	200
1	100

Then we drop the `id` column.

salary
300
200
100

Rename the salary column to `SecondHighestSalary`

SecondHighestSalary
300
200
100

Now we get the first two rows (via `.head(2)`).

SecondHighestSalary
300
200

The last of which is the second highest salary (via `.tail(1)`).

SecondHighestSalary
200

#### Implementation ---
# database ### Approach 1: Using sub-query and `LIMIT` clause #### Algorithm Sort the distinct salary in descend order and then utilize the [`LIMIT`](https://dev.mysql.com/doc/refman/5.7/en/select.html) clause to get the second highest salary. ```sql SELECT DISTINCT Salary AS SecondHighestSalary FROM Employee ORDER BY Salary DESC LIMIT 1 OFFSET 1 ``` However, this solution will be judged as 'Wrong Answer' if there is no such second highest salary since there might be only one record in this table. To overcome this issue, we can take this as a temp table. The complete code is as follows: #### Implementation ```sql SELECT (SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC LIMIT 1 OFFSET 1) AS SecondHighestSalary ; ``` ### Approach 2: Using `IFNULL` and `LIMIT` clause #### Algorithm Another way to solve the 'NULL' problem is to use `IFNULL` function as below. The `IFNULL` function will return the first argument if it is not `NULL`, otherwise it returns the second argument. This gives us the correct solution of *one row* containing `NULL` (if there is no such second highest salary) instead of just an empty table. #### Implementation ```sql SELECT IFNULL( (SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC LIMIT 1 OFFSET 1), NULL) AS SecondHighestSalary ```

Second Highest Salary

Problem Description

Problem Description

Answer Panel