QUALIFY (Beta) - Hologres - Alibaba Cloud Documentation Center

The QUALIFY clause filters the results of window functions in a SELECT statement. This topic covers the syntax and usage of the QUALIFY clause.

The QUALIFY clause filters the results of window functions, similar to how the HAVING clause filters the results of aggregate functions. The QUALIFY clause is evaluated after window functions are computed. The clauses in a SELECT statement execute in the following order:

FROM
WHERE
GROUP BY
HAVING
WINDOW
QUALIFY
DISTINCT
ORDER BY
LIMIT

Syntax

QUALIFY <predicate>

A statement that includes QUALIFY has this 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. The `predicate` looks like a `HAVING` clause but omits the `HAVING` keyword. It can also contain a window function.

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:
- column_list
- predicate
You can reference expressions in the column_list, including window functions, by their column aliases.
QUALIFY supports aggregations and subqueries in its predicate. For aggregations, the rules are the same as for a HAVING clause.
QUALIFY is a reserved keyword.
The Snowflake syntax for QUALIFY is not part of the ANSI standard.

Examples

The QUALIFY clause simplifies queries that filter the results of window functions. Without QUALIFY, this filtering requires a nested subquery. The following examples use the ROW_NUMBER() function to return only the first row from each partition. Create a table:

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

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

The query returns the following result:

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

Use QUALIFY:

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

The query returns the following result:

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

Reference a column alias in the QUALIFY clause:

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

The query returns the following result:

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