Number 176

https://leetcode.com/problems/second-highest-salary/

Problem Statement

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

My implementation for this was very sad.

I wrote -

SELECT salary 
FROM (SELECT * FROM Employee ORDER BY salary DESC LIMIT 2) A 
ORDER BY salary ASC LIMIT 1;

This is a classic example of incomplete implementation. This will work fine if there are cases where there is a “second highest salary.” But it falls flat when there’s no Second salary and NULL is to be returned.

One of the correct implementations, it turned out, was to use the dense_rank function, like so -

SELECT max(salary) AS SecondHighestSalary
FROM (SELECT salary, 
DENSE_RANK() OVER(ORDER BY salary DESC) AS rnk
FROM Employee) AS sub  
WHERE rnk=2;

Now this is a query with a lot of parts. Let’s break it down.

The best way to break a SQL query down is to from the innermost functions, outwards.

The first thing that’s happening here is -

SELECT salary, 
DENSE_RANK() OVER(ORDER BY salary DESC) AS rnk
FROM Employee;

Let’s call this TableA.

What this does is return two columns - the salary and its rank in the table as ordered by Salary in a Descending order.

The next step is to get the entry from this table where the rank is 2 -

SELECT max(salary) AS SecondHighestSalary
FROM TableA AS sub  
WHERE rnk=2;

But why the max function?

Even removing it will have returned the exact same value as without - but only in the case where there are no competing rows for rank=2. In case there are two salaries that are equal, and both have rank=2, then we can get just one of them using the Aggregate function max.