QUALIFY

Overview

QUALIFY filters the rows produced by window functions (ROW_NUMBER(), RANK(), LAG(), etc.), the same way HAVING filters aggregated groups and WHERE filters raw rows. Without QUALIFY, filtering on a window function result requires wrapping the query in a subquery or CTE just to apply the filter in an outer WHERE clause -- QUALIFY avoids that boilerplate entirely.

Copy code
SELECT schema_name, function_name,
       row_number() OVER (PARTITION BY schema_name ORDER BY function_name) AS rn
FROM duckdb_functions()
QUALIFY rn < 3;

Without QUALIFY, the same query needs a subquery:

Copy code
SELECT * FROM (
  SELECT schema_name, function_name,
         row_number() OVER (PARTITION BY schema_name ORDER BY function_name) AS rn
  FROM duckdb_functions()
) t
WHERE rn < 3;

Referencing a window function directly

QUALIFY can reference a window function inline, without it being aliased in the SELECT list:

Copy code
SELECT schema_name, function_name
FROM duckdb_functions()
QUALIFY row_number() OVER (PARTITION BY schema_name ORDER BY function_name) = 1;

Clause position and DuckDB's angle

QUALIFY sits after the (optional) WINDOW clause and before ORDER BY in query execution order -- logically: WHERE -> GROUP BY -> HAVING -> WINDOW -> QUALIFY -> SELECT -> ORDER BY. DuckDB has strong native support for QUALIFY, which pairs naturally with its efficient window function execution, making patterns like "top N rows per group," "deduplicate keeping the latest record," or "find the first event per session" a single flat query instead of a nested one.

Copy code
-- Latest order per customer, no subquery
SELECT customer_id, order_id, order_date
FROM orders
QUALIFY row_number() OVER (PARTITION BY customer_id ORDER BY order_date DESC) = 1;

QUALIFY is also implemented by Snowflake and BigQuery, though it is not part of core ANSI SQL.