Can you use a window function in a WHERE clause?
SQLLevel: IntermediateWindow Functions
Can you use a window function in a WHERE clause?
Hints
- Which rows belong to the partition and frame for each current row?
- Check ordering, ties, frame boundaries and the difference from GROUP BY.
Answer
No. Window functions are evaluated after WHERE, GROUP BY, and HAVING. To filter on a window function result, wrap it in a CTE or subquery.
-- This FAILS
SELECT *, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
FROM employees
WHERE rn <= 5;
-- This works
WITH ranked AS (
SELECT *, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
FROM employees
)
SELECT * FROM ranked WHERE rn <= 5;
Source and licence: SQL Interview Questions — Arda Kocadoru · MIT