If you've ever worked with window functions in SQL, you've probably run into a familiar frustration: you can't filter them directly with WHERE or HAVING. The traditional workaround? Wrap everything in a CTE or subquery. But there's a better way: QUALIFY.
What is QUALIFY?
QUALIFY is a SQL clause that lets you filter results based on window function conditions. Think of it as the WHERE clause specifically designed for window functions. Just as WHERE filters rows and HAVING filters aggregated groups, QUALIFY filters the results of window functions—making your queries cleaner and more readable.
The Problem: Filtering Window Functions
Let's look at a common scenario. Say you want to find the top 5 most expensive orders for each customer:
SELECT
o_custkey AS customer,
o_totalprice AS order_price,
ROW_NUMBER() OVER(PARTITION BY o_custkey ORDER BY o_totalprice DESC) AS row_num
FROM orders
Now you want to filter where row_num <= 5. Your first instinct might be to add WHERE row_num <= 5, but that won't work—window functions are computed after the WHERE clause runs. Similarly, HAVING row_num <= 5 will fail because HAVING is meant for grouped aggregates, not window functions.
The Old Way: Using CTEs
The traditional solution involves wrapping your query in a Common Table Expression (CTE):
WITH ranked_orders AS (
SELECT
o_custkey AS customer,
o_totalprice AS order_price,
ROW_NUMBER() OVER(PARTITION BY o_custkey ORDER BY o_totalprice DESC) AS row_num
FROM orders
)
SELECT *
FROM ranked_orders
WHERE row_num <= 5
This works, but it adds extra nesting and makes your query harder to scan at a glance.
The Better Way: Using QUALIFY
With QUALIFY, you can filter window functions directly in the main query:
SELECT
o_custkey AS customer,
o_totalprice AS order_price,
ROW_NUMBER() OVER(PARTITION BY o_custkey ORDER BY o_totalprice DESC) AS row_num
FROM orders
QUALIFY row_num <= 5
Much cleaner, right? The query reads naturally from top to bottom without the detour through a CTE.
How to Write Your Query with QUALIFY
When writing a query with QUALIFY, here's the order you'll write your clauses:
- SELECT
- FROM
- WHERE
- GROUP BY
- HAVING
- QUALIFY
- ORDER BY
- LIMIT
Flexible Syntax Options
QUALIFY offers flexibility in how you reference window functions. All three of these approaches are valid:
Option 1: Reference the alias
SELECT
o_custkey AS customer,
o_totalprice AS order_price,
ROW_NUMBER() OVER(PARTITION BY o_custkey ORDER BY o_totalprice DESC) AS row_num
FROM orders
QUALIFY row_num <= 5
Option 2: Repeat the window function
SELECT
o_custkey AS customer,
o_totalprice AS order_price,
ROW_NUMBER() OVER(PARTITION BY o_custkey ORDER BY o_totalprice DESC) AS row_num
FROM orders
QUALIFY ROW_NUMBER() OVER(PARTITION BY o_custkey ORDER BY o_totalprice DESC) <= 5
Option 3: Omit the column entirely
SELECT
o_custkey AS customer,
o_totalprice AS order_price
FROM orders
QUALIFY ROW_NUMBER() OVER(PARTITION BY o_custkey ORDER BY o_totalprice DESC) <= 5
The third option is particularly useful when you need the window function for filtering but don't actually want it in your output.
Why QUALIFY Matters
QUALIFY might seem like a small addition to SQL, but it represents a significant improvement in query ergonomics. It eliminates unnecessary subqueries, reduces nesting, and makes your intent immediately clear. For anyone working regularly with window functions—whether you're doing analytics, building rankings, or identifying duplicates—QUALIFY is a feature worth adopting.
Not all SQL databases support QUALIFY yet (it's available in systems like Snowflake, BigQuery, and newer versions of other platforms), but for those that do, it's quickly becoming the preferred way to work with window functions.