Question 390737 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Second Highest Salary not workin with ifnull()

Write a solution to find the second highest salary from the Employee table. If there is no second highest salary, return null.

This is my code:

select ifnull(x.salary,NULL) as SecondHighestSalary
from(
select *, dense_rank() over( order by salary) as rnk
from Employee)x
where x.rnk =2

It is passing all test case but not able to pass only 1 test case.

Employee:

id salary
1 100

Output:

SecondHighestSalary

Expected output:

SecondHighestSalary
null

Still don’t know why Isnull is not working.

Can someone please tell me why 1 test case is failing?

Tags:

2

Answers


  1. 0

    try:

    SELECT 
    (
        SELECT DISTINCT salary 
        FROM Employee 
        ORDER BY salary DESC 
        LIMIT 1 OFFSET 1
    ) AS SecondHighestSalary;
    Login or Signup to reply.
  2. 0

    You can use a good, old subquery with MAX rather than a window function. This will return NULL if no second highest salary exists:

    SELECT 
  MAX(salary) AS SecondHighestSalary
FROM Employee
WHERE 
  salary < (SELECT MAX(salary)
             FROM Employee);

    See this sample fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search