Why no windowed functions in where clauses?

sql

window-functions

sql-best-practices

query-optimization

byNikita Barsukov·Dec 22, 2024

You can filter on windowed functions like ROW_NUMBER() by enclosing them in a subquery or CTE (Common Table Expression). The WHERE clause precedes window function operations, therefore it can't access them directly.

Take a look at this SQL Example with a CTE:

WITH seating_arrangement AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY party ORDER BY appetite) AS seat_no 
  -- Those who eat less, get seated closer to the loo!
  FROM guests
)
SELECT * FROM seating_arrangement WHERE seat_no = 1;

In this example, the query brings back the first guest per group (partitioned by party).

The logic behind SQL query processing

In grasping logical query processing, it's beneficial to study the insights of Itzik Ben-Gan, a known authority in this area. His work highlights the step-wise execution of SQL queries, emphasizing that a SELECT statement commences with the FROM clause, undergoes filtering through WHERE, GROUP BY, and ultimately, processes window functions. This sequence not only guarantees accurate results, but also predictability of your queries' behavior.

How to apply filters on window functions

Given that window functions are processed later in the sequence of a query operation, placing them within a subquery or the FROM clause of a CTE can solve this predicament. Afterwards, the outer query's WHERE clause can filter the results using the calculated window function results.

For the SQL virtuosos, note that databases like Teradata and Snowflake support a QUALIFY clause. This clause enables direct filtering on window functions, much like how the HAVING clause functions but on a row level.

Adopting best practices and workarounds

In an environment such as SQL Server, which still does not support a QUALIFY-like feature, one should heed the following practices for optimum results:

Utilize window functions inside a subquery or CTE, then refer them in the outer query.
Let's hope that SQL Server developers treat us with this functionality in the future for an easier way to filter based on window functions.

Staying updated with SQL advances

As a SQL developer aiming to arm yourself with the latest SQL weapons, be on the lookout for updates from database systems that include QUALIFY-like features broadening the scope of window function usage. Meanwhile, staying proficient with SQL's operational order is an invaluable trait for any SQL pro.

Real-world uses for window functions

Consider it for: Pagination

Need to paginate results for a complex report? ROW_NUMBER() operates perfectly with OFFSET-FETCH or LIMIT clauses, allowing you to navigate pages like you're flipping a book.

Great for: De-duplicating data

Struggling with duplicate records? Window functions come to the rescue for identifying and retaining unique records. ROW_NUMBER() or RANK() assigns unique numbers to duplicates for easy filtering.

Quite useful for: Running totals

Dealing with financial data where running total matters? SUM(...) OVER() computes cumulative totals over a set offering you a dynamic financial analysis in a single query.

explain-codes / Sql / Why no windowed functions in where clauses?

Linked

Sql Row_Number() function in Where Clause



In SQL / MySQL, what is the difference between "ON" and "WHERE" in a join statement?



How to use multiple WITH statements in one PostgreSQL query?



Filter Table Before Applying Left Join



How to get the first and last record from an SQL query?

