Select the nth highest value in a column and null if it doesn't exist

user_995862 0
Viewed 2291

I'm trying to solve the LeetCode problem https://leetcode.com/problems/second-highest-salary/description/; my solution so far (see also http://sqlfiddle.com/#!9/4752cb/1) is:

SELECT Salary AS SecondHighestSalary 
FROM Employee 
ORDER BY Salary DESC LIMIT 1, 1;

The problem is that my solution is failing on the following test case:

enter image description here

In other words, it is simply returning no results rather than returning NULL. How can I make it return NULL if there is no second-highest salary?

Update

Following Return a value if no record is found, I tried to encapsulate the query in a sub-query:

SELECT (SELECT Salary 
FROM Employee 
ORDER BY Salary DESC LIMIT 1, 1) AS SecondHighestSalary;

However, this fails on a different test case in which there are two employees with the same salary:

enter image description here

In this case, we are apparently also supposed to return NULL. How can I adapt the 'closer to a solution' query above to handle this?

mysql
7 Answers

Here is a query which finally passes the tests:

SELECT (SELECT DISTINCT Salary 
FROM Employee 
ORDER BY Salary DESC LIMIT 1, 1) AS SecondHighestSalary;

It seems more intuitive than the other solutions, no?

user_9958620

Select another row that returns null, using union all so order is preserved, then return only the first row of that:

SELECT * FROM
(
    SELECT Salary AS SecondHighestSalary 
    FROM Employee 
    ORDER BY Salary DESC LIMIT 1, 1
    UNION ALL
    SELECT NULL
)
LIMIT 1
user_2561960

Here OFFSET 1 means without 1st one (EX:if you don't want to display first 2 then OFFSET will be 2)

SELECT
    Salary AS SecondHighestSalary 
FROM
    Employee

ORDER BY
    Salary

LIMIT 1 OFFSET 1;
user_52049030

With the current query, we could just wrap it in another query, and use an aggregate function 

   SELECT MAX(v.salary) AS SecondHighestSalary
     FROM (
            SELECT e.Salary
              FROM Employee e
             ORDER BY e.Salary DESC
             LIMIT 1, 1
          ) v

What should second highest salary be given this set ...

 id  salary
 --  ------
  1    1000
  2    1000
  4     750

Should we return 1000, or return 750? If we want to return 750, we could do something like this:

   SELECT MAX(s.salary) AS SecondHighestSalary
     FROM ( SELECT MAX(e.salary) AS max_salary
              FROM Employee e
          ) h
     JOIN Employee s
       ON s.salary < h.max_salary
user_1077440

Use IFNULL :

SELECT
  IFNULL( (
  SELECT DISTINCT
    `salary` 
  FROM
    `Employee` 
  ORDER BY
    `salary` DESC LIMIT 1, 1), NULL )
)
AS `SecondHighestSalary`

Returns NULL if first query returns nothing.

user_74101100

Safe with Distinct excluding null values:

SELECT distinct Salary AS SecondHighestSalary 
FROM Employee where salary is not null ORDER BY salary desc limit 1,1
user_65642760

you can first get the top two values then handle the case of null and the normal case separately

select case
when min(top2.salary)=max(top2.salary) then null
else min(top2.salary) end
as SecondHighestSalary
from 
(select e.salary from employee e order by e.salary desc limit 2) as top2;
user_87828860
Related