Why can't you use a window function directly in a WHERE clause, and how do you work around it?

How to think about it

This is one of those errors that surprises nearly everyone the first time they hit it. The fix is simple once you understand why it happens — and understanding the why also unlocks WHERE vs. HAVING, alias scoping, and the top-N-per-group pattern all at once.

Why the error happens

SQL processes clauses in this logical order:

FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY

Window functions live in the SELECT phase — step 5. WHERE runs at step 2. When WHERE executes, no window values exist yet. The database cannot filter on something it has not computed.

The broken version

-- ERROR: window functions not allowed in WHERE
SELECT
    employee_id,
    salary,
    ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn
FROM employees
WHERE rn <= 3;    -- rn does not exist yet at this point

Every major database (PostgreSQL, MySQL, SQL Server, BigQuery) throws an error here. The alias rn is simply not visible to WHERE.

Fix 1: subquery

Wrap the window query in a subquery, then filter in the outer query where rn is already computed:

SELECT employee_id, salary
FROM (
    SELECT
        employee_id,
        salary,
        ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn
    FROM employees
) t
WHERE t.rn <= 3;

Fix 2: CTE (preferred for readability)

A CTE makes the intent explicit — “first rank, then filter”:

WITH ranked AS (
    SELECT
        employee_id,
        dept_id,
        salary,
        ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn
    FROM employees
)
SELECT employee_id, dept_id, salary
FROM ranked
WHERE rn <= 3;

Both approaches produce identical results. Most modern optimisers treat a CTE and a subquery the same way for simple cases like this.

Run the working version here — try changing rn <= 3 to rn = 1 to get only the top earner per department:

The same rule applies to HAVING

HAVING filters groups before the SELECT phase, so window function aliases are also invisible there:

-- Also invalid:
SELECT dept_id, AVG(salary),
    RANK() OVER (ORDER BY AVG(salary) DESC) AS dept_rank
FROM employees
GROUP BY dept_id
HAVING dept_rank <= 5;   -- dept_rank not computed yet

Wrap it in a CTE the same way.