How to fix window functions are not allowed in WHERE clause in PostgreSQL

PostgreSQLINTERMEDIATEMEDIUMUpdated Dec 22, 2025· 191 views

PostgreSQL forbids window functions like ROW_NUMBER(), RANK(), or SUM() OVER() in WHERE clauses because window functions execute after WHERE filters. To filter based on window function results, compute them in a subquery or CTE first, then apply your WHERE condition in the outer query.

Quick fix

Fixes window functions are not allowed in WHERE clause

-- Instead of this (which fails):
SELECT employee_id, salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC) as salary_rank
FROM employees
WHERE ROW_NUMBER() OVER (ORDER BY salary DESC) <= 10;

-- Do this:
WITH ranked_employees AS (
  SELECT employee_id, salary,
         ROW_NUMBER() OVER (ORDER BY salary DESC) as salary_rank
  FROM employees
)
SELECT employee_id, salary, salary_rank
FROM ranked_employees
WHERE salary_rank <= 10;
  1. Wrap your query in a CTE to compute window functions first
  2. Use a subquery if you prefer inline expressions
  3. Apply window function filters with QUALIFY clause (PostgreSQL 15+)
  4. Use DISTINCT ON as an alternative for some ranking scenarios
postgresql — error
-- Instead of this (which fails):
SELECT employee_id, salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC) as salary_rank
FROM employees
WHERE ROW_NUMBER() OVER (ORDER BY salary DESC) <= 10;
-- Do this:
WITH ranked_employees AS (
  SELECT employee_id, salary,
         ROW_NUMBER() OVER (ORDER BY salary DESC) as salary_rank
  FROM employees
)
SELECT employee_id, salary, salary_rank
FROM ranked_employees
WHERE salary_rank <= 10;
window functions are not allowed in WHERE clause

What this error means

Window functions in PostgreSQL (such as ROW_NUMBER(), RANK(), DENSE_RANK(), LAG(), LEAD(), SUM() OVER(), and others) operate on sets of rows defined by the OVER clause. They are permitted only in the SELECT list and ORDER BY clause of a query, not in WHERE, GROUP BY, or HAVING clauses. This restriction exists because of SQL's logical query execution order: WHERE clauses filter rows before grouping and window calculations occur, so the window function results don't yet exist when WHERE is evaluated. When you try to reference a window function in a WHERE clause, PostgreSQL immediately rejects the query with this error. The standard workaround is to wrap your window function query in a subquery or Common Table Expression (CTE), calculate the window function values there, and then filter on those calculated values in the outer query's WHERE clause.

Typical symptoms

  • Query fails with "ERROR: window functions are not allowed in WHERE clause" when trying to filter on ROW_NUMBER(), RANK(), or similar functions
  • Attempting to use expressions like "WHERE ROW_NUMBER() OVER(...) = 1" or "WHERE SUM(amount) OVER(...) > 100" causes immediate syntax error
  • Complex queries with PARTITION BY and ORDER BY in window functions cannot be filtered directly
  • Migration from other databases (like SQL Server or Oracle) that may handle window functions differently fails in PostgreSQL
  • ORM-generated queries that attempt to filter on calculated window function columns produce this error

Common causes

  1. Direct use of window functions like ROW_NUMBER(), RANK(), DENSE_RANK() in WHERE clause predicates
  2. Trying to filter aggregates with OVER() clauses (SUM() OVER(), AVG() OVER(), etc.) in WHERE conditions
  3. Misunderstanding SQL execution order - WHERE evaluates before window functions are calculated
  4. Porting queries from databases that allow window functions in different clauses without modification
  5. ORM or query builder generating SQL that assumes window functions can be used everywhere like regular columns

How to fix "window functions are not allowed in WHERE clause"

1Wrap your query in a CTE to compute window functions first

The most readable solution is to use a Common Table Expression (CTE) with the WITH clause:

sql
-- Instead of this (which fails):
SELECT employee_id, salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC) as salary_rank
FROM employees
WHERE ROW_NUMBER() OVER (ORDER BY salary DESC) <= 10;

-- Do this:
WITH ranked_employees AS (
  SELECT employee_id, salary,
         ROW_NUMBER() OVER (ORDER BY salary DESC) as salary_rank
  FROM employees
)
SELECT employee_id, salary, salary_rank
FROM ranked_employees
WHERE salary_rank <= 10;

The CTE computes the window function in the inner query, then the outer query filters on the calculated column. This approach is clean, maintainable, and works for any window function.

2Use a subquery if you prefer inline expressions

If you don't want to use a CTE, a subquery in the FROM clause achieves the same result:

sql
SELECT employee_id, salary, salary_rank
FROM (
  SELECT employee_id, salary,
         ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) as salary_rank
  FROM employees
) ranked
WHERE salary_rank = 1;

This finds the highest-paid employee in each department by calculating ROW_NUMBER() in the subquery, then filtering where rank equals 1 in the outer query. Subqueries perform identically to CTEs but may be harder to read with complex logic.

3Apply window function filters with QUALIFY clause (PostgreSQL 15+)

PostgreSQL 15 introduced the QUALIFY clause, which allows filtering on window functions without subqueries:

sql
SELECT employee_id, salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC) as salary_rank
FROM employees
QUALIFY ROW_NUMBER() OVER (ORDER BY salary DESC) <= 10;

QUALIFY evaluates after window functions are computed, making this the most concise syntax. However, it requires PostgreSQL 15 or later, so check your server version with SELECT version(); before using it. For older versions, stick with CTEs or subqueries.

4Use DISTINCT ON as an alternative for some ranking scenarios

If you're using ROW_NUMBER() to get the first row per partition, consider DISTINCT ON instead:

sql
-- Instead of:
WITH ranked AS (
  SELECT DISTINCT ON (department_id)
         employee_id, department_id, salary
  FROM employees
  ORDER BY department_id, salary DESC
)
SELECT * FROM ranked;

-- This works without window functions:
SELECT DISTINCT ON (department_id)
       employee_id, department_id, salary
FROM employees
ORDER BY department_id, salary DESC;

DISTINCT ON picks the first row for each unique value of the specified column(s), achieving the same result as ROW_NUMBER() = 1 partitioned queries without needing window functions at all. This only works for simple "top 1 per group" cases.

Advanced notes

Window functions execute in the SELECT phase of query processing, after WHERE, GROUP BY, and HAVING but before ORDER BY and LIMIT. This execution order is why they cannot appear in WHERE clauses - the filtered row set must be determined before window calculations can occur. The QUALIFY clause (PostgreSQL 15+) specifically exists to filter on window functions and executes after the window function phase. For complex analytics, consider materialized CTEs (WITH ... AS MATERIALIZED) if you need to reuse expensive window calculations multiple times. When migrating from databases like SQL Server where window functions might work in different contexts, always test queries against PostgreSQL specifically. Performance-wise, CTEs and subqueries with window functions are optimized similarly by PostgreSQL's planner, so choose based on readability rather than performance concerns.

Frequently asked questions

What does "window functions are not allowed in WHERE clause" mean?
Window functions in PostgreSQL (such as ROW_NUMBER(), RANK(), DENSE_RANK(), LAG(), LEAD(), SUM() OVER(), and others) operate on sets of rows defined by the OVER clause. They are permitted only in the SELECT list and ORDER BY clause of a query, not in WHERE, GROUP BY, or HAVING clauses. This restriction exists because of SQL's logical query execution order: WHERE clauses filter rows before grouping and window calculations occur, so the window function results don't yet exist when WHERE is evaluated.
How do I fix "window functions are not allowed in WHERE clause"?
PostgreSQL forbids window functions like ROW_NUMBER(), RANK(), or SUM() OVER() in WHERE clauses because window functions execute after WHERE filters. To filter based on window function results, compute them in a subquery or CTE first, then apply your WHERE condition in the outer query.
What causes "window functions are not allowed in WHERE clause"?
Direct use of window functions like ROW_NUMBER(), RANK(), DENSE_RANK() in WHERE clause predicates Trying to filter aggregates with OVER() clauses (SUM() OVER(), AVG() OVER(), etc.) in WHERE conditions Misunderstanding SQL execution order - WHERE evaluates before window functions are calculated
Is the "window functions are not allowed in WHERE clause" error serious?
It is a medium-severity error — more of a blocker than a risk. The steps above resolve it.

Official resources & further reading