QUALIFY

The QUALIFY clause is used to filter the results of WINDOW functions. This filtering of results is similar to how a HAVING clause filters the results of aggregate functions applied based on the GROUP BY clause.

The QUALIFY clause avoids the need for a subquery or WITH clause to perform this filtering (much like HAVING avoids a subquery). An example using a WITH clause instead of QUALIFY is included below the QUALIFY examples.

Note that this is filtering based on WINDOW functions, not necessarily based on the WINDOW clause. The WINDOW clause is optional and can be used to simplify the creation of multiple WINDOW function expressions.

The position of where to specify a QUALIFY clause is following the WINDOW clause in a SELECT statement (WINDOW does not need to be specified), and before the ORDER BY.

Examples

Each of the following examples produces the same output.

Filter based on a window function defined in the QUALIFY clause:

Query

SELECT    team,    player,    -- In this example the player_rank column in the select clause is for reference    row_number() OVER (PARTITION BY team ORDER BY points DESC) AS player_rankFROM scoresQUALIFY    row_number() OVER (PARTITION BY team ORDER BY points DESC) < 3;

Result

 team  | player | player_rank-------+--------+------------- Hawks | Ann    |           1 Hawks | Bob    |           2 Owls  | Dan    |           1 Owls  | Eve    |           2

Filter based on a window function defined in the SELECT clause:

Query

SELECT    team,    player,    row_number() OVER (PARTITION BY team ORDER BY points DESC) AS player_rankFROM scoresQUALIFY    player_rank < 3;

Result

 team  | player | player_rank-------+--------+------------- Hawks | Ann    |           1 Hawks | Bob    |           2 Owls  | Dan    |           1 Owls  | Eve    |           2

Filter based on a window function defined in the QUALIFY clause, but using the WINDOW clause:

Query

SELECT    team,    player,    -- In this example the player_rank column in the select clause is for reference    row_number() OVER my_window AS player_rankFROM scoresWINDOW    my_window AS (PARTITION BY team ORDER BY points DESC)QUALIFY    row_number() OVER my_window < 3;

Result

 team  | player | player_rank-------+--------+------------- Hawks | Ann    |           1 Hawks | Bob    |           2 Owls  | Dan    |           1 Owls  | Eve    |           2

Filter based on a window function defined in the SELECT clause, but using the WINDOW clause:

Query

SELECT    team,    player,    row_number() OVER my_window AS player_rankFROM scoresWINDOW    my_window AS (PARTITION BY team ORDER BY points DESC)QUALIFY    player_rank < 3;

Result

 team  | player | player_rank-------+--------+------------- Hawks | Ann    |           1 Hawks | Bob    |           2 Owls  | Dan    |           1 Owls  | Eve    |           2

Equivalent query based on a WITH clause (without a QUALIFY clause):

Query

WITH ranked_players AS (    SELECT        team,        player,        row_number() OVER (PARTITION BY team ORDER BY points DESC) AS player_rank    FROM scores)SELECT    *FROM ranked_playersWHERE    player_rank < 3;

Result

 team  | player | player_rank-------+--------+------------- Hawks | Ann    |           1 Hawks | Bob    |           2 Owls  | Dan    |           1 Owls  | Eve    |           2

Syntax