Skip to content
LakeBench
ProblemsCommunityPricing
Sign inStart practicing
Back
  1. Home
  2. Interview prep
  3. Can window functions be used in WHERE?

SQL · Window Functions

Can window functions be used in WHERE?

Mediumsql-25
windowwherecte

Question

Can window functions be used in WHERE?

Solution

Generally no.

This fails in standard SQL:

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

WHERE runs before the window result exists.

Use a subquery or CTE:

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).

🎯 Put this concept into practice

Solidify this answer with real hands-on interview drills in the browser studio.

Open related drill →
PreviousNext