QUALIFY Clause Overview: Filter Window Function Results - Hologres

Evaluated after all window functions are computed, QUALIFY eliminates the need for a subquery or WITH clause just to filter on a window function result.

The clauses in a SELECT statement execute in this order:

FROM
WHERE
GROUP BY
HAVING
WINDOW
QUALIFY
DISTINCT
ORDER BY
LIMIT

Syntax

QUALIFY <predicate>

Full statement form:

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

Parameters

Parameter	Description
column_list	Follows the rules for the projection clause in a `SELECT` statement.
data_source	A table or another table-like source, such as a view.
predicate	An expression that filters results after aggregate and window functions are computed. Structured like a `HAVING` clause but without the `HAVING` keyword. Can contain a window function.

Usage notes

QUALIFY requires at least one window function in at least one of the following locations: column_list or predicate.
You can reference expressions in the column_list, including window functions, by their column aliases.
QUALIFY supports aggregations and subqueries in its predicate. Aggregation rules follow the same rules as HAVING.
QUALIFY is a reserved keyword.
The Snowflake syntax for QUALIFY is not part of the ANSI standard.

Examples

The following examples use the ROW_NUMBER() window function to return only the first row from each partition.

Create the sample table first:

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

Without QUALIFY: nested subquery

SELECT *
FROM (
    SELECT
        i,
        p,
        o,
        ROW_NUMBER() OVER (PARTITION BY p ORDER BY o) AS row_num
    FROM qt
)
WHERE row_num = 1;

Result:

+---+---+---+---------+
| I | P | O | ROW_NUM |
|---+---+---+---------|
| 1 | A | 1 |       1 |
| 3 | B | 1 |       1 |
+---+---+---+---------+

With QUALIFY: window function in the predicate

Define the window function directly in the QUALIFY predicate:

SELECT
    i,
    p,
    o
FROM qt
QUALIFY ROW_NUMBER() OVER (PARTITION BY p ORDER BY o) = 1;

Result:

+---+---+---+
| I | P | O |
|---+---+---|
| 1 | A | 1 |
| 3 | B | 1 |
+---+---+---+

With QUALIFY: column alias in the predicate

Define the window function in the column_list and reference its alias in the QUALIFY predicate:

SELECT
    i,
    p,
    o,
    ROW_NUMBER() OVER (PARTITION BY p ORDER BY o) AS row_num
FROM qt
QUALIFY row_num = 1;

Result:

+---+---+---+---------+
| I | P | O | ROW_NUM |
|---+---+---+---------|
| 1 | A | 1 |       1 |
| 3 | B | 1 |       1 |
+---+---+---+---------+