How to Use QUALIFY in Snowflake: Syntax and Examples
In Snowflake, use the
QUALIFY clause to filter query results based on conditions applied to window functions. It works like WHERE but filters after window functions are processed, allowing you to select rows such as the top N per group easily.Syntax
The QUALIFY clause filters rows after window functions are calculated. It is placed after WHERE, GROUP BY, and HAVING clauses but before ORDER BY.
Basic syntax:
SELECT columns, window_function() OVER (PARTITION BY col ORDER BY col) AS alias FROM table [WHERE condition] [GROUP BY columns] [HAVING condition] QUALIFY window_function_condition;
Here, window_function_condition is a condition on the window function result, like ROW_NUMBER() = 1.
sql
SELECT column1, ROW_NUMBER() OVER (PARTITION BY column2 ORDER BY column3 DESC) AS rn FROM table_name QUALIFY rn = 1;
Example
This example shows how to select the highest salary employee per department using QUALIFY with ROW_NUMBER().
sql
CREATE OR REPLACE TABLE employees (id INT, name STRING, department STRING, salary INT); INSERT INTO employees VALUES (1, 'Alice', 'Sales', 70000), (2, 'Bob', 'Sales', 60000), (3, 'Charlie', 'HR', 65000), (4, 'Diana', 'HR', 62000), (5, 'Eve', 'IT', 80000); SELECT id, name, department, salary FROM employees QUALIFY ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) = 1;
Output
ID | NAME | DEPARTMENT | SALARY
---|---------|------------|--------
1 | Alice | Sales | 70000
3 | Charlie | HR | 65000
5 | Eve | IT | 80000
Common Pitfalls
Common mistakes when using QUALIFY include:
- Trying to use
WHEREto filter on window functions, which is not allowed because window functions run afterWHERE. - Placing
QUALIFYbeforeGROUP BYorHAVING, which causes syntax errors. - Confusing
QUALIFYwithHAVING;HAVINGfilters aggregated groups, whileQUALIFYfilters rows after window functions.
Wrong example:
SELECT name, salary, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn FROM employees WHERE rn = 1;
This fails because WHERE cannot filter on rn.
Correct example:
SELECT name, salary, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn FROM employees QUALIFY rn = 1;
Quick Reference
| Clause | Purpose | Position in Query |
|---|---|---|
| WHERE | Filters rows before window functions | After FROM, before GROUP BY |
| GROUP BY | Groups rows for aggregation | After WHERE |
| HAVING | Filters groups after aggregation | After GROUP BY |
| QUALIFY | Filters rows after window functions | After HAVING, before ORDER BY |
| ORDER BY | Sorts final result | Last clause |
Key Takeaways
Use QUALIFY to filter rows based on window function results in Snowflake.
QUALIFY runs after WHERE, GROUP BY, and HAVING clauses but before ORDER BY.
You cannot filter window function results in WHERE; use QUALIFY instead.
QUALIFY is useful for selecting top N rows per group with functions like ROW_NUMBER().
Place QUALIFY correctly in the query to avoid syntax errors.
