Why Window Functions Cannot Be Used in WHERE Clause in SQL

1. Introduction

A window function in SQL calculates values using a specific set of rows (a window) defined in the query. These calculations are helpful for tasks like finding totals, rankings, or cumulative sums without changing the original data.

However, window functions can only be used in a query’s SELECT list and ORDER BY clause. We can’t use them elsewhere, such as in GROUP BY, HAVING, or WHERE clauses. These clauses run after processing the main query, which generates the rows used by the window function.

In this tutorial, we’ll explain why we can’t use window functions in the WHERE clause and show two workarounds. We’ll use the Baeldung’s University database in our examples. All the queries were tested on PostgreSQL 16.3, SQL Server 2019, and MySQL 8.0.39. 

2. Why Window Functions Aren’t Allowed in WHERE Clauses

Here’s the order in which an SQL engine processes a query:

• It starts with data retrieval (FROM, JOIN), applies filters (WHERE), groups data (GROUP BY), calculates aggregates, and applies group filters (HAVING).
• Next, it evaluates window functions, selects columns (SELECT), removes duplicates (DISTINCT), and combines results (UNION, etc.).
• Finally, it sorts (ORDER BY), skips rows (OFFSET), and limits results (LIMIT, FETCH, TOP).

As window functions operate on the result set generated after filtering, grouping, and aggregation (WHERE, GROUP BY, and HAVING clauses), we can’t use them within a WHERE clause.

2.1. Example: Using Window Function in WHERE Clause

Let’s try to use a window function in the WHERE clause:

SELECT id, name, gpa, 
        RANK() OVER (ORDER BY gpa DESC) AS Rank
FROM Student
WHERE RANK() OVER (ORDER BY gpa DESC) <= 3;

In this example, we try to use the RANK() function in the WHERE clause to find the three students with the highest GPA, which leads to the following error in PostgreSQL (a similar occurs in MySQL and SQL Server):

We can use a subquery or a common table expression (CTE) as a workaround.

3. Alternative Ways to Use Window Functions Effectively

With a CTE or subquery, we first calculate the window function. Then, we filter the results in the outer query.

3.1. Using a Common Table Expression

A CTE creates a temporary result table that includes the window function values. Then, we use this result in the WHERE clause of the main query.

For example, we can use a CTE to calculate the students’ ranks based on their GPA (excluding null values) and then filter the results to find the three students with the highest GPA:

WITH RankedStudents AS (
  SELECT id, name, gpa,
         RANK() OVER (ORDER BY gpa DESC) AS r
  FROM Student
  WHERE gpa IS NOT NULL
)
SELECT id, name, gpa, r
FROM RankedStudents
WHERE r <= 3;

Here, we use the RANK() window function in a CTE to rank the students based on their GPA in descending order, excluding those with NULL GPAs. We save those ranks in the r column of the CTE. Then, in the main part of the query, we specify the condition r <= 3 to retrieve those with the top 3 scores:

3.2. Using a Subquery

We can also use a subquery.

The execution order is similar to CTE. First, we calculate the window function within the subquery, and then, the main query filters the results:

SELECT *
FROM (
  SELECT id, name, gpa, 
         RANK() OVER (ORDER BY gpa DESC) AS r
  FROM Student
  WHERE gpa IS NOT NULL
) AS RankedStudents
WHERE r <= 3;

The results are the same as with the CTE:

4. Choosing Between CTEs and Subqueries

CTEs improve readability and are reusable within a larger query, which makes them a good choice for handling window functions in complex queries. However, they might consume more memory when dealing with larger datasets.

On the other hand, subqueries can be faster for straightforward, one-time calculations involving window functions but can become more challenging to read and manage in deeply nested queries.

The choice between a CTE and a subquery depends on users’ needs. CTEs are generally preferred for better code clarity and maintainability, while subqueries might perform better for simple queries where performance is a priority.

5. Conclusion

In this article, we discussed why window functions aren’t allowed in the WHERE clause. While useful for analyzing data and allowing calculations on specific groups of rows, we can’t use them in the WHERE clause because SQL processes it before applying window functions.

As alternatives, we can use CTEs and subqueries to achieve the same results as if direct conditioning on the window functions in the WHERE clause were possible.