SQL · Window Functions

Can window functions be used in WHERE?

Mediumwindowwherecte

Interview question

Can window functions be used in WHERE?

How to answer

The short answer

Generally no.

This fails in standard SQL:

SQL
SELECT *,
       ROW_NUMBER() OVER (...) AS rn
FROM employees
WHERE rn = 1;

WHERE runs before the window result exists.

Use a or :

SQL
WITH ranked AS (
    SELECT *,
           ROW_NUMBER() OVER (
               PARTITION BY department
               ORDER BY salary DESC
           ) AS rn
    FROM employees
)
SELECT *
FROM ranked
WHERE rn = 1;

Interviewers love this pattern (top-N per group with CTE + ROW_NUMBER).

Lock it in with a real drill

You remember an answer longer once you have used it. Try a hands-on problem on this concept.

Open related drill