# Filter on a window function

Shape / rows

A variant of [Filter rows](https://meridian.online/operations/filter-rows).

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

Page: <https://meridian.online/operations/filter-rows/filter-on-a-window-function>

## Status

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.

## What other tools call it

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

- Apache DataFusion: [`QUALIFY clause`](https://datafusion.apache.org/user-guide/sql/select.html) (read 2026-09-27)
- DuckDB: [`QUALIFY (clause)`](https://duckdb.org/docs/current/sql/query_syntax/qualify) (read 2026-09-27)