How to fix Window functions are not allowed in WHERE in PostgreSQL

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;