QUALIFY¶

Syntax¶

QUALIFY <predicate>

The general form of a statement with QUALIFY is similar to the following (some variations in order are allowed, but are not shown):

SELECT <column_list>
  FROM <data_source>
  [GROUP BY ...]
  [HAVING ...]
  QUALIFY <predicate>
  [ ... ]

column_list

This generally follows the rules for the projection clause of a SELECT statement.

data_source

The data source is usually a table, but can be another table-like data source, such as a view, UDTF (user-defined table function), etc.

predicate

The predicate is an expression that filters the result after aggregates and window functions are computed. The predicate should look similar to a HAVING clause, but without the keyword HAVING. In addition, the predicate can also contain window functions.

See the Examples section (in this topic) for predicate examples.

Usage notes¶

• The QUALIFY clause requires at least one window function to be specified in at least one of the following clauses of the SELECT statement:

  • The SELECT column list.

  • The filter predicate of the QUALIFY clause.

  Examples of each of these are shown in the Examples section below.

• Expressions in the SELECT list, including window functions, can be referred to by the column alias defined in the SELECT list.

• QUALIFY supports aggregates and subqueries in the predicate. For aggregates, the same rules as for the HAVING clause apply.

• The word QUALIFY is a reserved word.

• The Snowflake syntax for QUALIFY is not part of the ANSI standard.

Examples¶

The QUALIFY clause simplifies queries that require filtering on the result of window functions. Without QUALIFY, filtering requires nesting. The example below uses the ROW_NUMBER() function to return only the first row in each partition.

Create and load a table:

CREATE TABLE qt (i INTEGER, p CHAR(1), o INTEGER);
INSERT INTO qt (i, p, o) VALUES
    (1, 'A', 1),
    (2, 'A', 2),
    (3, 'B', 1),
    (4, 'B', 2);

Copy

This query uses nesting rather than QUALIFY:

SELECT * 
    FROM (
         SELECT i, p, o, 
                ROW_NUMBER() OVER (PARTITION BY p ORDER BY o) AS row_num
            FROM qt
        )
    WHERE row_num = 1
    ;
+---+---+---+---------+
| I | P | O | ROW_NUM |
|---+---+---+---------|
| 1 | A | 1 |       1 |
| 3 | B | 1 |       1 |
+---+---+---+---------+

Copy

This query uses QUALIFY:

SELECT i, p, o
    FROM qt
    QUALIFY ROW_NUMBER() OVER (PARTITION BY p ORDER BY o) = 1
    ;
+---+---+---+
| I | P | O |
|---+---+---|
| 1 | A | 1 |
| 3 | B | 1 |
+---+---+---+

Copy

You can also use QUALIFY to reference window functions that are in the SELECT column list:

SELECT i, p, o, ROW_NUMBER() OVER (PARTITION BY p ORDER BY o) AS row_num
    FROM qt
    QUALIFY row_num = 1
    ;
+---+---+---+---------+
| I | P | O | ROW_NUM |
|---+---+---+---------|
| 1 | A | 1 |       1 |
| 3 | B | 1 |       1 |
+---+---+---+---------+

Copy

You can see how the QUALIFY acts as a filter by removing the QUALIFY from the previous query and comparing the output:

SELECT i, p, o, ROW_NUMBER() OVER (PARTITION BY p ORDER BY o) AS row_num
    FROM qt
    ;
+---+---+---+---------+
| I | P | O | ROW_NUM |
|---+---+---+---------|
| 1 | A | 1 |       1 |
| 2 | A | 2 |       2 |
| 3 | B | 1 |       1 |
| 4 | B | 2 |       2 |
+---+---+---+---------+

Copy

The QUALIFY clause can also be combined with aggregates and can have subqueries in the predicate. For example:

SELECT c2, SUM(c3) OVER (PARTITION BY c2) as r
  FROM t1
  WHERE c3 < 4
  GROUP BY c2, c3
  HAVING SUM(c1) > 3
  QUALIFY r IN (
    SELECT MIN(c1)
      FROM test
      GROUP BY c2
      HAVING MIN(c1) > 3);

Copy