# Simple query which handles the NULL situation

• SELECT max(Salary)
FROM Employee
WHERE Salary < (SELECT max(Salary) FROM Employee)

Using max() will return a NULL if the value doesn't exist. So there is no need to UNION a NULL. Of course, if the second highest value is guaranteed to exist, using LIMIT 1,1 will be the best answer.

• I assume you meant `guaranteed`, not gangrened :)

• Great, thank you for sharing your code.

• what if we want to get the third highest ? and fourth …

• what if we want to get the third highest ? and fourth …

• I got a wrong answer,and can you tell me why it is wrong?Thank you!

SELECT Salary FROM Employee WHERE Salary < (SELECT MAX(Salary) FROM Employee) limit 1;

• your method will get a collection of salaries except of the highest

• i think it's a wrong answer,how about when there are two max num? like 2,2 ??

• select IFNULL( (select distinct e1.salary from Employee e1
where (select count(distinct e2.salary ) from Employee e2 where e2.salary > e1.salary) = 1) , null)

Here is my solution,you can change =1 to =2,=3 if you want to get the third highest or fourth

• but you use distinct in this case . if any number occurs twice ,you can not assure the answer is right

• why we couldn't use "distinct e2.Id"? Someone could help me make it.

• I think it's partially right but every time you use LIMIT, make sure you user ORDER BY too. Otherwise the outcome would be different.

• Easy solution with better runtime

SELECT distinct(Salary) FROM Employee
Union select null
ORDER BY Salary DESC LIMIT 1,1

• The OJ now requires you to rename the selected column as 'SecondHighestSalary'.

SELECT max(Salary) AS SecondHighestSalary
FROM Employee
WHERE Salary < (SELECT max(Salary) FROM Employee)

• got error of "Unknown column 'Salary' in 'order clause'"
@ramnath.medikonda said in Simple query which handles the NULL situation:

SELECT distinct(Salary) FROM Employee
Union select null
ORDER BY Salary DESC LIMIT 1,1

• @lguodongustc the question got changed recently, now its asking you to return SecondHighestSalary as alias for Salary. So below is the updated query.

SELECT distinct(Salary) AS SecondHighestSalary FROM Employee
Union select null
ORDER BY SecondHighestSalary DESC LIMIT 1,1

• I got a WA when I submit the same code as yours. Until I changed into this:

SELECT max(Salary) as SecondHighestSalary
FROM Employee
WHERE Salary < (SELECT max(Salary) FROM Employee)

• @eashion i got WA . my code is also same as yours.

• Not sure why the below query shows wrong answer

select distinct max(salary) from Employee e where salary < (SELECT max(salary) from Employee)

Looks like your connection to LeetCode Discuss was lost, please wait while we try to reconnect.