Shape / rows · All operations

Filter on a window function

A variant of Filter rows

Keep the rows whose window-function value meets a condition, such as the top row per partition.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

The DuckDB step

Keep the rows whose window value meets a condition

SQL
-- the top order of each product, and the orders above their own product's average
CREATE TEMP TABLE top_per_product AS
SELECT order_id, product, amount
FROM orders
QUALIFY row_number() OVER (PARTITION BY product ORDER BY amount DESC NULLS LAST, order_id) = 1;
CREATE TEMP TABLE above_average AS
SELECT order_id, product, amount
FROM orders
QUALIFY amount > avg(amount) OVER (PARTITION BY product);
-- the same top rows from a subquery over the window function, for a database without QUALIFY
CREATE TEMP TABLE top_per_product_subquery AS
SELECT order_id, product, amount
FROM (SELECT *, row_number() OVER (PARTITION BY product ORDER BY amount DESC NULLS LAST, order_id) AS rn FROM orders)
WHERE rn = 1;
SELECT * FROM top_per_product ORDER BY product;
SELECT * FROM above_average ORDER BY order_id;
SELECT CASE WHEN ((SELECT list(order_id ORDER BY order_id) FROM top_per_product) = [2, 6, 10] AND (SELECT list(order_id ORDER BY order_id) FROM above_average) = [2, 6, 8, 10] AND (SELECT list(order_id ORDER BY order_id) FROM top_per_product_subquery) = [2, 6, 10]) THEN 'as expected' ELSE error('the top order per product was not 2, 6 and 10, or the orders above their product average were not 2, 6, 8 and 10') END AS verdict;
  • The statement builds its own data.
  • It needs nothing installed beyond DuckDB itself.
  • The statement checks its own result and raises an error when it is wrong: “the top order per product was not 2, 6 and 10, or the orders above their product average were not 2, 6, 8 and 10”.
  • It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

What other tools call it

Meridian's classification; an independent blind rating agreed on 95.7% of 324 rows (variant, version 3).

Apache DataFusion (1)
  • QUALIFY clauseread 2026-09-27

    Filter rows after window functions are evaluated

DuckDB (1)
  • QUALIFY (clause)read 2026-09-27

    Keep rows matching a condition on a window function

Background

In DuckDB and Snowflake, the QUALIFY clause filters on the results of window functions, much as HAVING filters on the results of aggregate functions.[1][2] In PostgreSQL, window functions are forbidden in WHERE, GROUP BY and HAVING clauses, because they logically execute after those clauses.[3] In PostgreSQL, rows can be filtered after the window calculations with a sub-select, as in keeping the first two rows of each department by row_number.[3]

When to use it

In DuckDB, the QUALIFY clause avoids the need for a subquery or WITH clause to perform this filtering, much like HAVING avoids a subquery.[1] In Snowflake, the syntax for QUALIFY is not part of the ANSI standard.[2] Where a query must also run on a database without QUALIFY, write the sub-select form instead. (Meridian's view)

In PostgreSQL, the rows a window function considers are those left by the WHERE, GROUP BY and HAVING clauses, so a row removed by WHERE is not seen by any window function.[3] Decide whether a condition should shape the window or only trim the result: put it in WHERE to rank among the rows that remain, and after the window to rank among them all. (Meridian's view)

ROW_NUMBER can be non-deterministic: if the sort key is not unique, rows with the same key may be given different row numbers each time the query runs.[4] In PostgreSQL, row_number numbers the rows of each partition in the order of the ORDER BY clause, with tied rows numbered in an unspecified order.[3] When keeping the top row of each partition, add a unique column such as an id to the ORDER BY, so that the same row is kept on every run. (Meridian's view)

The method

In Snowflake, a SELECT statement's clauses are typically evaluated in the order FROM, WHERE, GROUP BY, HAVING, WINDOW, QUALIFY, DISTINCT, ORDER BY and LIMIT.[2] In Snowflake, QUALIFY requires at least one window function, in the SELECT column list or in the QUALIFY predicate.[2] In DuckDB, a QUALIFY condition can use a window function defined in the QUALIFY clause itself or one defined in the SELECT clause.[1] In DuckDB, the QUALIFY clause follows the WINDOW clause in a SELECT statement, and comes before the ORDER BY.[1]

Compared with the alternatives

In PostgreSQL, window functions execute after non-window aggregate functions, so an aggregate call can appear in the arguments of a window function but not the reverse.[3]

Sources

  1. [1]QUALIFY Clause – DuckDBdocumentation
  2. [2]QUALIFY | Snowflake Documentationdocumentation
  3. [3]PostgreSQL: Documentation: 18: 3.5. Window Functionsdocumentation
  4. [4]Select (SQL) - Wikipediasecondary source