qualify clause (window functions)

Neither the where nor the having clause allow window functions—but the qualify clause does.

 SELECT *
   FROM …
QUALIFY ROW_NUMBER() OVER(PARTITION BY … ORDER BY …) <= 3

The example is a top-n per group query: it returns the first three rows for each partition.

A better supported query to get the same result pushes the window function into the select clause of a sub-query and uses the where clause in the outer query for filtering.

SELECT *
  FROM (SELECT *
             , ROW_NUMBER() OVER(PARTITION BY … ORDER BY …) AS rn
          FROM …
       ) AS sq
 WHERE sq.rn <= 3

Note that this query returns the extra column “rn”.

Related

• over(partition by …)

• over(order by …)

• row_number()

Normative References

The qualify clause is not defined in the SQL standard ISO/IEC 9075-2:2023.