176. Second Highest Salary

select salary 
from employee
order by salary desc limit 2 
having salary <> max(salary)

select salary 
from employee 
order by salary desc limit 1 
having salary not in (select max(salary) from employee);

The above-stated query is not working and giving a response as

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘having salary <> max(salary)’ at line 2

As having is used for aggregator functions this should work IMO.

Please help me out by explaining why it is not working also I am trying to learn SQL query please provide me with a resource if possible.

Answer

Another option is using window function :

WITH cte AS
(
SELECT *,
   DENSE_RANK() OVER (ORDER BY salary Desc) AS Rnk
FROM employee
)
SELECT cte.salary
FROM cte
WHERE Rnk=2;

Note if you have salaries 5000, 5000, 3000 , as @Thorsten Kettner mentioned in the comment, the second highest salary would be 3000.

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=90c67881bcf6fd8f0c50a5a351f6b9ca