Qualify Clause in Snowflake: Definition and Usage
QUALIFY clause in Snowflake filters the results of window functions after they are computed. It works like a WHERE clause but applies to the output of window functions, letting you select rows based on ranking or other window calculations.How It Works
The QUALIFY clause lets you filter rows based on the results of window functions, such as ROW_NUMBER(), RANK(), or DENSE_RANK(). Think of it like a filter that happens after Snowflake calculates these special values for each row.
Imagine you have a list of students with their scores, and you want to pick the top scorer in each class. First, Snowflake assigns a rank to each student within their class using a window function. Then, the QUALIFY clause filters the list to keep only the top-ranked students. This is different from WHERE because WHERE filters before window functions run, so it can't use their results.
Example
This example shows how to use QUALIFY to get the highest paid employee in each department.
SELECT department, employee_name, salary, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank FROM employees QUALIFY rank = 1;
When to Use
Use QUALIFY when you want to filter rows based on window function results without using subqueries or common table expressions. It simplifies queries that find top N records per group, remove duplicates, or rank data.
For example, in sales reports, you might want to show only the top 3 salespeople per region. Instead of writing complex nested queries, QUALIFY lets you write clear and concise SQL.
Key Points
- QUALIFY filters after window functions are calculated.
- It works like
WHEREbut for window function results. - Helps simplify queries that rank or filter grouped data.
- Supported in Snowflake and some other SQL platforms.
Key Takeaways
QUALIFY filters rows based on window function results.QUALIFY runs after window functions, unlike WHERE.