QUALIFY clause

The QUALIFY clause is used to filter the results of window functions. This filtering of results is similar to how the HAVING clause filters the results of aggregate functions used in queries with the GROUP BY clause.

The QUALIFY clause avoids the need for a subquery or expression with WITH to perform this filtering (just as HAVING avoids subqueries).

Syntax

QUALIFY <predicate>

The general form of expressions with QUALIFY usually looks like this (some variations in order are allowed):

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

Parameters

  • <column_list>
    List of SELECT clause.

  • <data_source>
    The data source is usually a table, but it can be another data source similar to a table, such as a view, a custom table function, etc.

  • <predicate>
    A predicate is an expression that filters the result after evaluating aggregate and window functions. A predicate is similar to the HAVING clause, but without the HAVING keyword itself. In addition, the predicate may contain window functions.

Examples

Let’s create and fill the 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);

This query uses a nested structure 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 |
+---+---+---+---------+

And 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 |
+---+---+---+

We use QUALIFY to refer to window functions that are in the SELECT clause:

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 |
+---+---+---+---------+

A QUALIFY clause can also be combined with aggregate functions and may contain subqueries:

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);