Fix "window functions are not allowed in WHERE" in PostgreSQL
ERROR: window functions are not allowed in WHERE
Window functions are worked out near the end of the query, after WHERE has already picked the rows. So WHERE can't use them. The fix is always the same: calculate the window function in an inner query, give it a name, and filter on that name in the outer query.
Why it happens
- Keeping the top row per group with WHERE RANK() OVER (...) = 1.
- Comparing a row with the previous one using WHERE ... LAG(...) OVER (...).
- Expecting QUALIFY from Snowflake or BigQuery, which Postgres doesn't have.
- The same rule applies to GROUP BY and HAVING, where the error names that clause instead.
How to fix it
1. Rank in a CTE, filter outside
Fails
SELECT name, department, salary FROM employees WHERE DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) = 1;
Works
WITH ranked AS (
SELECT name, department, salary,
DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rnk
FROM employees
)
SELECT name, department, salary
FROM ranked
WHERE rnk = 1;Using the alias directly (WHERE rnk = 1 in the same query) doesn't work either: it gives column "rnk" does not exist. The alias only exists once the inner query has run.
2. Wrap LAG() in a subquery
Fails
SELECT user_id, login_date FROM logins WHERE login_date - LAG(login_date) OVER (PARTITION BY user_id ORDER BY login_date) = 1;
Works
SELECT user_id, login_date
FROM (
SELECT user_id, login_date,
LAG(login_date) OVER (PARTITION BY user_id ORDER BY login_date) AS prev_login
FROM logins
) AS l
WHERE login_date - prev_login = 1;This keeps logins that came the day after the previous one. Subtracting two dates in Postgres gives the number of days between them.
3. Use DISTINCT ON for one row per group
Fails
SELECT account_id, merchant, amount FROM transactions WHERE ROW_NUMBER() OVER (PARTITION BY account_id ORDER BY amount DESC, id) = 1;
Works
SELECT DISTINCT ON (account_id) account_id, merchant, amount FROM transactions ORDER BY account_id, amount DESC, id;
DISTINCT ON is a Postgres shortcut: it keeps the first row for each account_id, using the ORDER BY to decide which row is first.
Try it yourself
This is the broken query, running on real PostgreSQL in your browser. Press Run to see the error explained, then fix it.
Starting Postgres in your browser…
Real Postgres in your browser. Edit the query and run it, or open it in the full playground.
Practice so it doesn't happen in an interview
- Top 3 earners per departmentWindow functions · DENSE_RANK()
- Largest charge per accountROW_NUMBER() · deterministic tie-breaker
- Third-highest salary per departmentNth highest · DENSE_RANK() vs ROW_NUMBER()
- Consecutive-day loginsLAG() · date arithmetic