Filtering on Window Function Results
What you'll learn: How to filter query results based on window function calculations, even though WHERE and HAVING can't directly reference them.
The Core Problem
Window functions are incredibly powerful, but they come with a catch: you cannot reference them directly in a WHERE or HAVING clause. Why? Because window functions are evaluated after those filtering stages have already completed. If you try something like WHERE ROW_NUMBER() OVER (...) = 1, you'll get an error.
Think of it like trying to grade papers before the students have finished taking the test—the timing just doesn't work.
The Solution: Subqueries or CTEs
To filter on window function results, you need to calculate them first in an inner query or CTE, then filter in an outer query. The window function runs in the inner scope, producing a column you can treat like any other in the outer scope.
Pattern 1: Using a Subquery
SELECT *
FROM (
SELECT employee_id, salary,
RANK() OVER (ORDER BY salary DESC) as salary_rank
FROM employees
) ranked_employees
WHERE salary_rank <= 3;
Pattern 2: Using a CTE (usually cleaner)
WITH ranked_employees AS (
SELECT employee_id, salary,
RANK() OVER (ORDER BY salary DESC) as salary_rank
FROM employees
)
SELECT *
FROM ranked_employees
WHERE salary_rank <= 3;
Both approaches work identically: the window function calculates in the inner scope, creating a new column that's then available for normal filtering in the outer SELECT.
Key Takeaway: Since WHERE and HAVING cannot directly filter on window functions, wrap your windowed query in a subquery or CTE, then apply filters in the outer query where the window results are regular columns.