SQL error

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

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

The exact 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 aliased window function rn
When the alias is referenced. Writing the function out in WHERE gives misuse of window function row_number().

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 is not recognized.

Why it happens

In SQL's logical processing order, WHERE filters rows before window functions are computed, so at the moment WHERE 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. This describes which results are available to each clause, not the physical plan chosen by the optimizer.

The fix

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

The query that fails

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

Running it raises WHERE clause cannot contain window functions.

The corrected query

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

Open the corrected query in the playground

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, a WHERE 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 aliased window function rn` When the alias is referenced. Writing the function out in WHERE gives misuse of window function row_number().

Related errors

Fix “Aggregate Functions Are Not Allowed in WHERE” in SQL
WHERE filters rows before grouping, so aggregates do not exist yet. Use HAVING.
Fix “Column Not Found” When Filtering on a SQL Alias
In logical order, WHERE comes before SELECT assigns aliases, so many engines reject the alias there.
Fix “Column Must Appear in the GROUP BY Clause” in SQL
A selected column is neither grouped nor aggregated, so this engine cannot derive one value for the group.