QUALIFYQuery Techniques
A post-aggregation filter clause for window functions, analogous to HAVING for aggregate functions, avoiding the need for a subquery.
SQL Server and Snowflake clause that filters window function results, analogous to HAVING but for window functions. Qualify enables filtering on ROW_NUMBER, RANK, and other window results directly in the query, eliminating the need for a subquery.
| Database System | Support Status | Since Version | Notes |
|---|---|---|---|
| MySQL | ✗ Not Supported | — | No QUALIFY clause. Use a subquery or CTE to filter on window function results. |
| PostgreSQL | ✗ Not Supported | — | No QUALIFY clause. Standard workaround is a subquery or CTE: wrap the windowed query and apply WHERE on the outer query. |
| SQL Server | ✗ Not Supported | — | No QUALIFY clause. Use a subquery or CTE with a WHERE filter on the window function column alias. |
| Oracle | ✗ Not Supported | — | No QUALIFY clause as of Oracle 21c. Use an inline view or CTE. |
| SQLite | ✗ Not Supported | — | No QUALIFY clause. Use a subquery or CTE. |
QUALIFY is a non-standard extension supported natively in Teradata, BigQuery, Snowflake, DuckDB, and Databricks — but not in any of the five engines covered here. Its appeal is readability: it removes the need to wrap a windowed query in a subquery just to filter on the window result. The standard-portable workaround is consistent across all engines: place the window expression in a CTE or subquery, then filter in an outer WHERE clause. Example: WITH ranked AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY grp ORDER BY col) AS rn FROM t) SELECT * FROM ranked WHERE rn = 1. Some engines (PostgreSQL, SQL Server) allow referencing a window function alias in WHERE if wrapped in a subquery, but not in the same query level where it is defined.