Todays leetcode challenge was about handling ‘NULL’ by finding the second highest salary from table called ‘Employee’ with columns ‘ID’ and ‘Salary’.

Table ‘Employee’

Id	Salary
1	300
2	100
3	200

Expected Output:

Salary
200

My Failed Solution:

SELECT Salary AS SecondHighestSalary FROM
(SELECT Salary, ROW_NUMBER() OVER (ORDER BY Id) AS rown FROM Employee
) WHERE rown = 2;

Although, using the windows function did a good job of finding the second highest salary, it failed in one of the test cases. The test case had only one record and since there was only one record, ‘NULL’ is to be returned. With my solution above for the test case, it did not return anything.

After viewing other solution submitted for hints, I came up with the following workable solution.

SELECT NVL( (
SELECT Distinct Salary FROM(
SELECT Salary, DENSE_RANK() OVER (ORDER BY Salary DESC) AS rown FROM Employee) where rown=2),Null) AS secondhighestsalary
FROM Dual;
Runtime: 663 ms

‘NVL’ is a function which returns ‘NULL’ when the first arguement provided is false.

Other Submitted Solution

SELECT MAX(Salary) AS SecondHighestSalary FROM Employee
WHERE Salary < (Select MAX(Salary) FROM Employee);
Runtime: 602 ms

This solution came as a surprise. ‘MAX()’ is another solution that returns ‘NULL’ when the conditions are not fulfiled.

SELECT MAX(salary) AS SecondHighestSalary FROM 
(
SELECT SALARY, DENSE_Rank() OVER (ORDER BY SALARY DESC) RNUM
FROM Employee
 )
 WHERE RNUM = 2;
Run: 512 ms

Another version of solution similar to mine but this solution uses ‘MAX()’ instead of the ‘NVL()’ function.

• Facebook
• Instagram
• LinkedIn
• GitHub