Skip to content
Quantoros

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

Related questions