QUALIFY
Filter rows based on a window function directly, without wrapping the query in a CTE or subquery. WHERE runs before window functions evaluate, so window filters need a second query level, unless the engine supports QUALIFY. Supported in DuckDB, Snowflake, BigQuery, Teradata, Databricks. Not in Postgres, MySQL, or SQLite.
Syntax #
SELECT *
FROM table_name
QUALIFY window_function_call OVER (...) <predicate>;Example #
QUALIFY is dialect-specific. Ship it to DuckDB, Snowflake, BigQuery, Teradata, or Databricks and the query works. Ship the same SQL to Postgres, MySQL, SQL Server, or SQLite and it fails to parse. For portable code, fall back to the CTE + WHERE rn = 1 pattern: one extra level of nesting, but works on every engine. If your stack is a mix (Snowflake in prod, Postgres in dev), standardize on the portable form to avoid "works on my machine" divergence.