I know this will not work but can someone explain why?
SELECT * FROM employees HAVING salary = MAX(salary);
The code does not work with WHERE clause because its filtering against an aggregate result. My understanding is that HAVING should work with aggregate functions. Why does this not work?
1
The HAVING clause requires the GROUP BY clause because it filters data that the GROUP BY clause produces. The GROUP BY clause groups rows with the same values into summary rows, and the HAVING clause then filters those groups.
If you want the max salary across the entire table then you could do this:
select max(salary) from employees;
1