3

Using MySQL v8.0 right now.

The question is:

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

My dummy data is:

Create table If Not Exists Employee (id int, salary int);
insert into Employee (id, salary) values 
(1, 100);

My ideal output is like this:

+------+--------+
|  id  | salary |
+------+--------+
| NULL |  NULL  |
+------+--------+

I used DENSE_RANK as a more straightforward way for me to solve this question:

WITH sub AS (SELECT id,
       salary,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS num
       FROM Employee )
SELECT id, salary
FROM sub
WHERE num = 2

But I have a problem exporting NULL when there's no second highest salary. I tried IFNULL, but it didn't work. I guess it's because the output is not actually null but just empty.

Thank you in advance.

union77
  • 103
  • 7
  • (edit) Yep, ifnull() only works if there's a record (or more) returned. There may be a better option, but you could use UNION ALL to append the null values `select null, null`. Then order the results by `salary desc` so non-null values appear first, then add `limit 1` to return a single record. That'll return the second highest salary if there is one. Otherwise, it will return `null, null` – SOS Mar 03 '22 at 03:47
  • 1
    if there is more than one id with the second highest salary, do you want more than one row returned? – ysth Mar 03 '22 at 04:12
  • @ysth If there are more than one ids with the same second highest salary, output them all. That's why I put `DENSE_RANK` and `num=2`. Sorry I didn't clarify it. – union77 Mar 03 '22 at 23:28
  • 1
    @SOS Can you be more specific about how to UNION ALL, and then order the unioned output in salary desc and limit 1? – union77 Mar 03 '22 at 23:29
  • @ericzheng0404 - I just posted a more detailed explanation as an answer with a fiddle example – SOS Mar 03 '22 at 23:30

2 Answers2

2
WITH sub AS (
    SELECT id,
           salary,
           DENSE_RANK() OVER (ORDER BY salary DESC) AS num
    FROM Employee 
)
SELECT id, salary
FROM sub
WHERE num = 2
UNION ALL
SELECT NULL, NULL
WHERE 0 = ( SELECT COUNT(*)
            FROM sub 
            WHERE num = 2 );

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=31f5afb0e7e5dce9c2c128ccc49a6f42

Akina
  • 39,301
  • 5
  • 14
  • 25
0

Just making your query a subquery and left joining from a single-row producing subquery seems to me the simplest approach:

select id, salary
from (select null) at_least_one_row
left join (
    select id, salary
    from (
        select id, salary, dense_rank() over (order by salary desc) as num
        from Employee
    ) ranked_employees
    where num = 2
) second_highest_salary on true

(I usually prefer a subquery to a cte that's only used once; I find that obfuscatory.)

ysth
  • 96,171
  • 6
  • 121
  • 214