Question 419472 in Mysql Question posted in
The official MySQL documentation can be found here.

MySQL: Why HAVING clause couldn't deal with column of window function?

I have such MySQL code:

SELECT 
    departmentId, 
    name, 
    salary, 
    DENSE_RANK() OVER (PARTITION BY departmentId ORDER BY salary DESC) AS salary_rank
FROM Employee
HAVING departmentId = 1

This works properly as expected but if I change the HAVING clause as

SELECT 
    departmentId, 
    name, 
    salary, 
    DENSE_RANK() OVER (PARTITION BY departmentId ORDER BY salary DESC) AS salary_rank
FROM Employee
HAVING salary_rank = 1

It wouldn’t get passed.

Why HAVING clause is not abled to do such filtering, for HAVING clause "taking effect after query"?

Tags:

2

Answers


  1. 0

    While MySQL has overloaded the HAVING operator to allow referring to aliases, it does not work with window functions. The reason for this is that the window functions generally evaluate last in the pipeline. This means that, in your second query, the dense rank calculation would not have completed when it would be needed to filter. In this case, the workaround is to simply use a proper subquery:

    WITH cte AS (
    SELECT departmentId, name, salary, 
           DENSE_RANK() OVER (PARTITION BY departmentId ORDER BY salary DESC) AS salary_rank
    FROM Employee
)

SELECT departmentId, name, salary
FROM cte
WHERE salary_rank = 1;
    Login or Signup to reply.
  2. 0

    Why HAVING clause is not abled to do such filtering, for HAVING clause "taking effect after query"?

    Formally, it should be assumed that window functions are processed/evaluated after processing the HAVING clause and calculating all output columns, except those in expressions for which some window function is used, but before processing the ORDER BY.

    Thus, expressions that use window functions have not yet been evaluated at the time of processing the HAVING clause.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search