Window functions like ROW_NUMBER(), RANK(), and DENSE_RANK() cannot be used directly in WHERE clauses because they are evaluated after the WHERE clause filters are processed. This limitation exists due to SQL's logical order of operations. Use a CTE (Common Table Expression) or subquery to compute window functions first, then filter in the outer query.
This error occurs when you try to use a window function in a WHERE clause. Window functions perform calculations across rows related to the current row in a result set. However, PostgreSQL processes query clauses in a specific order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY. Since window functions are only evaluated during the SELECT phase, they cannot be used in WHERE, GROUP BY, or HAVING clauses—they simply don't exist yet at that point in query execution. The error is a syntax and logic error: PostgreSQL cannot evaluate something that doesn't exist in the current phase of query execution.
Wrap the window function in a WITH clause, then reference it in the outer query:
WITH ranked_sales AS (
SELECT
id,
product_id,
salesperson_id,
amount,
ROW_NUMBER() OVER(PARTITION BY product_id ORDER BY amount DESC) AS rn
FROM sale
)
SELECT id, product_id, salesperson_id, amount
FROM ranked_sales
WHERE rn = 1;This CTE computes the row numbers first, then the WHERE clause filters by the computed value. CTEs are generally preferred because they're more readable and maintainable.
If you prefer not to use a CTE, you can use a subquery:
SELECT id, product_id, salesperson_id, amount
FROM (
SELECT
id,
product_id,
salesperson_id,
amount,
ROW_NUMBER() OVER(PARTITION BY product_id ORDER BY amount DESC) AS rn
FROM sale
) AS ranked_sales
WHERE rn = 1;Both approaches achieve the same result—window functions are computed in the inner query/CTE, then the outer query filters by them.
Common window function errors:
✗ Wrong: WHERE ROW_NUMBER() OVER(ORDER BY id) = 1
✓ Correct: Window function must be computed first in SELECT or CTE
✗ Wrong: GROUP BY RANK() OVER(PARTITION BY category ORDER BY sales DESC)
✓ Correct: Move to SELECT or CTE, filter in outer query
Window functions can ONLY appear in:
- SELECT list
- ORDER BY clause
They CANNOT appear in:
- WHERE clause
- GROUP BY clause
- HAVING clause
Use CTEs when:
- You have multiple window functions or complex logic
- Code readability is important
- You want to chain multiple CTEs together
- Performance is not critical
Use Subqueries when:
- You have a simple single window function
- Performance is a priority (simpler for the query optimizer)
- You prefer inline queries
Example with multiple CTEs:
WITH sales_ranked AS (
SELECT *, ROW_NUMBER() OVER(PARTITION BY product_id ORDER BY amount DESC) AS rn
FROM sale
),
top_sales AS (
SELECT * FROM sales_ranked WHERE rn = 1
),
with_avg AS (
SELECT *, AVG(amount) OVER(PARTITION BY product_id) AS avg_amount
FROM top_sales
)
SELECT * FROM with_avg WHERE amount > avg_amount;SQL Execution Order (Why Window Functions Are Evaluated Late):
1. FROM clause (data retrieval)
2. WHERE clause (row filtering)
3. GROUP BY clause (grouping)
4. HAVING clause (group filtering)
5. Window functions (computed on result set)
6. SELECT clause (column selection)
7. DISTINCT (remove duplicates)
8. ORDER BY (sorting)
9. LIMIT/OFFSET (pagination)
This order is why window functions cannot appear in WHERE, GROUP BY, or HAVING—they don't exist yet. Window functions operate on the result set generated AFTER all filtering and grouping.
Common Window Functions:
- ROW_NUMBER(): Sequential numbering within partitions
- RANK(): Ranking with gaps for ties
- DENSE_RANK(): Ranking without gaps
- NTILE(n): Divides rows into n buckets
- LEAD()/LAG(): Access next/previous rows
- FIRST_VALUE()/LAST_VALUE(): First/last value in frame
- SUM/AVG/COUNT() OVER(): Running aggregates
Performance Considerations:
CTEs are optimized by PostgreSQL's query planner and often perform similarly to subqueries. Use EXPLAIN ANALYZE to compare:
EXPLAIN ANALYZE
WITH ranked AS (
SELECT *, ROW_NUMBER() OVER(ORDER BY id) AS rn
FROM large_table
)
SELECT * FROM ranked WHERE rn <= 10;ERROR: syntax error at end of input
Syntax error at end of input in PostgreSQL
Bind message supplies N parameters but prepared statement requires M
Bind message supplies N parameters but prepared statement requires M in PostgreSQL
Multidimensional arrays must have sub-arrays with matching dimensions
Multidimensional arrays must have sub-arrays with matching dimensions
ERROR: value too long for type character varying
Value too long for type character varying
insufficient columns in unique constraint for partition key
How to fix "insufficient columns in unique constraint for partition key" in PostgreSQL