176. Second Highest Salary-CodePudding

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.

CodePudding user response：

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

CodePudding user response：

We want the second highest salary.

<!-- -->
>     create table employee (salary int);
>     insert into employee values (1),(2),(3),(4),(5),(6),(7),(8);
> 
> <pre>
> ✓
> 
> ✓
> </pre>

<!-- -->
>       select salary 
>       from employee 
>       order by salary desc 
>       limit 1, 1 ;
> 
> <pre>
> | salary |
> | -----: |
> |      7 |
> </pre>

*db<>fiddle [here](https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=ddf303748ee5620b2334b95621024840)*