Aggregate function MAX giving unexpected output when used inside a HAVING clause

The Table

Id	Salary
1	100
2	200
3	300

I want to find out all the salaries lesser the highest salary. For doing so, i have come up with the following query-

SELECT salary FROM Employee HAVING salary<max(salary);

Which yields the output-

{"headers": ["salary"], "values": [[100]]}

But my expected output here is

{"headers": ["salary"], "values": [[100], [200]]}

If i used the constant value 300 instead of max(salary) i can obtain my expected value.

I’m unable to understand why using the aggregate function MAX in HAVING is resulting in the wrong output here. Also, i do not want to accomplish this using WHERE because i’m trying to use this query as a sub-query for a larger question i’m trying to answer.

Answer

If you are using MySQL then you can use window function.

select salary from 
( select salary,rank()over(order by salary desc) rnk from employee) t
where rnk>1

Or you can use subquery:

Select salary
FROM Employee
WHERE salary < (SELECT MAX(salary) FROM Employee)