Teradata introduced The QUALIFY clause in a SELECT SQL query years ago. It’s been followed over the years by Oracle, Snowflake, Google BigQuery, Databricks, and other relational database systems. It is not part of the SQL standard.
The Problem
In an SQL statement, you cannot filter the WHERE clause by a window function (e.g., a statistical function call, usually followed by an OVER clause). This is because any window function call is internally evaluated after the WHERE clause evaluation.
SELECT name
FROM (VALUES ('apples'), ('oranges'), ('nuts')) AS fruits(name)
WHERE row_number() OVER (ORDER BY name) >= 2
ORDER BY name;
SELECT name,
row_number() over (ORDER BY name) as rnk
FROM (VALUES ('apples'), ('oranges'), ('nuts')) AS fruits(name)
WHERE rnk >= 2
ORDER BY name;
When writing a query to select the 1st two fruits sorted alphabetically, When executing the above queries, you will get the following error.
ORA-30483: window functions are not allowed here
The Solution
Using a Subquery
The typical fix is to separate a subquery, in which all window functions are evaluated. The outer query was now applying the WHERE filter on fields already evaluated before:
WITH cte AS (SELECT name, row_number() OVER (ORDER BY name) AS rn
FROM (VALUES ('apples'), ('oranges'), ('nuts')) AS fruits(name)
ORDER BY name)
SELECT *
FROM cte
WHERE rn >= 2;
Using QUALIFY Clause
QUALIFY is a convenient clause that allows filtering on window functions in the same query after the window function values have been calculated. All these correct versions return “nuts” and “oranges” in this order.
SELECT name, row_number() OVER (ORDER BY name) AS rnk
FROM (values ('apples'), ('oranges'), ('nuts')) AS fruits(name) QUALIFY rn >= 2
ORDER BY name;
SELECT name
FROM (VALUES ('apples'), ('oranges'), ('nuts')) AS fruits(name) QUALIFY row_number() OVER (ORDER BY name) >= 2
ORDER BY name;