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 error surprises nearly everyone the first time. The fix is simple once you see why it happens — and the why unlocks WHERE vs HAVING, alias scoping, and the top-N-per-group pattern all at once.

SQL processes clauses in a fixed order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY. Window functions live in the SELECT phase, step 5. WHERE runs at step 2. So when WHERE executes, the window value simply doesn’t exist yet:

-- 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 isn't computed until step 5

Every major engine rejects this — rn isn’t visible to WHERE.

A worked example — the wrapper fix

Wrap the ranked query in a CTE (or subquery), and filter in the outer query where rn already exists:

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

id	name	dept_id	salary
1	Aarav	1	120000
2	Bea	1	110000
3	Chen	1	95000
5	Eli	2	90000
6	Farah	2	75000
7	Gita	2	60000

The top three per department come through (Dara, rank 4 in dept 1, is filtered out). The CTE computes rn first, so by the time the outer WHERE rn <= 3 runs, the value is a real column — exactly the staging that the single-query version lacked. A subquery does the same job; most optimisers treat the two identically here.

The same rule extends to HAVING (step 4, also before SELECT): a RANK() OVER (...) alias is invisible there too, so wrap that in a CTE as well.