SQL error

Fix “Window Functions Are Not Allowed in WHERE” in SQL

Window functions run after WHERE, so you cannot filter on one directly.

The symptom

You add a row_number() or rank() and try to keep just the top row per group, but the database rejects it with an error like"window functions are not allowed in WHERE", or the alias simply is not recognized.

Why it happens

SQL clauses run in a fixed order. WHERE filters rowsbefore window functions are computed, so at the momentWHERE runs, the rn column does not exist yet. The same is true for referencing it by alias: window functions are evaluated after WHERE, GROUP BY, and HAVING, and only just before ORDER BY and LIMIT.

The fix

Compute the window in an inner query, then filter its result in an outer query. A CTE (WITH) reads the most clearly.

A subquery in the FROM clause does the same job if you prefer not to use a CTE.

Same idea, as a subquery

select product_name, category_id
from (
  select product_name,
         category_id,
         row_number() over (partition by category_id order by price desc) as rn
  from products
)
where rn = 1

DuckDB shortcut

DuckDB (which powers SQLShed) supports QUALIFY, aWHERE for window results, with no wrapper query needed:… qualify row_number() over (partition by category_id order by price desc) = 1. It is not standard SQL, so keep the CTE form for databases without it.

How other engines word it

Engine	Message
DuckDB	`Binder Error: WHERE clause cannot contain window functions!`
PostgreSQL	`window functions are not allowed in WHERE`
SQL Server	`Windowed functions can only appear in the SELECT or ORDER BY clauses.`
MySQL	`Window function 'rn' is not allowed in the WHERE clause`
SQLite	`misuse of window function rn()`, or a plain`no such column: rn` if the alias is referenced instead