Bird
Raised Fist0
SnowflakeHow-ToBeginner · 3 min read

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 WHERE to filter on window functions, which is not allowed because window functions run after WHERE.
  • Placing QUALIFY before GROUP BY or HAVING, which causes syntax errors.
  • Confusing QUALIFY with HAVING; HAVING filters aggregated groups, while QUALIFY filters 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

ClausePurposePosition in Query
WHEREFilters rows before window functionsAfter FROM, before GROUP BY
GROUP BYGroups rows for aggregationAfter WHERE
HAVINGFilters groups after aggregationAfter GROUP BY
QUALIFYFilters rows after window functionsAfter HAVING, before ORDER BY
ORDER BYSorts final resultLast 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.