Why does my HAVING caluse return nothing?-CodePudding

The problem is to find the Second Highest Salary from the employees table. However my HAVING clause returns nothing, and I have no clue why. My logic is I will just group by salary, and the condition I set in the HAVING clause is that group by salary, only if salary != the maximum salary.

This way I thought I excluded the highest value for salary in the grouping, and then I will only display the first record, which I thought would be the 2nd highest salary.

SELECT salary                  
FROM Employee
GROUP BY salary
HAVING salary != MAX(salary)
ORDER BY salary desc
LIMIT 1

CodePudding user response：

You don't need group by, order by or limit at all, you just can take the highest salary that is smaller than the maximum:

SELECT MAX(salary)
FROM employee
WHERE salary < (SELECT MAX(salary) FROM employee);

Grouping or ordering should be avoided whenever they are not required due to their high execution time. In case the table contains very many rows, they make the query slow.

CodePudding user response：

Use a subquery to get the max salary:

SELECT *                  
FROM Employee
WHERE salary != (SELECT MAX(salary) FROM Employee)
ORDER BY salary desc
LIMIT 1

Grouping is not required.

You can use a window function too:

SELECT *, ROW_NUMBER() OVER(ORDER BY salary desc) as rn   
FROM Employee
WHERE rn = 2