Filtering on a Window Result
Why you must wrap a window function in a subquery or CTE to filter on it.
Why You Cannot Filter a Window in WHERE
A frequent interview "gotcha": writing WHERE ROW_NUMBER() OVER (...) = 1 throws an error. Window functions are not allowed in WHERE, GROUP BY, or HAVING.
The reason is logical execution order. WHERE runs to select rows before window functions are evaluated. The window has not even been computed yet, so it cannot be referenced in a filter.
The Execution-Order Explanation
Window functions are computed in a dedicated phase that sits after FROM, WHERE, GROUP BY, and HAVING, but before the final ORDER BY and LIMIT.
So at the moment WHERE runs, the rank or row number does not exist. To filter on it, you must let the window finish first, then filter the produced column in an outer query layer.
All lessons in this course
- OVER, PARTITION BY and ORDER BY
- ROW_NUMBER for Unique Sequencing
- RANK vs DENSE_RANK on Ties
- Filtering on a Window Result