- Installation
- Guides
- Overview
- Data Import & Export
- CSV Import
- CSV Export
- Parquet Import
- Parquet Export
- Query Parquet
- HTTP Parquet Import
- S3 Parquet Import
- S3 Parquet Export
- JSON Import
- JSON Export
- Excel Import
- Excel Export
- SQLite Import
- PostgreSQL Import
- Meta Queries
- ODBC
- Python
- Install
- Execute SQL
- Jupyter Notebooks
- SQL on Pandas
- Import from Pandas
- Export to Pandas
- SQL on Arrow
- Import from Arrow
- Export to Arrow
- Relational API on Pandas
- Multiple Python Threads
- DuckDB with Ibis
- DuckDB with Polars
- DuckDB with Vaex
- DuckDB with DataFusion
- DuckDB with fsspec Filesystems
- SQL Features
- SQL Editors
- Data Viewers
- Documentation
- Connect
- Data Import
- Overview
- CSV Files
- JSON Files
- Multiple Files
- Parquet Files
- Partitioning
- Appender
- Insert Statements
- Client APIs
- Overview
- C
- Overview
- Startup
- Configure
- Query
- Data Chunks
- Values
- Types
- Prepared Statements
- Appender
- Table Functions
- Replacement Scans
- API Reference
- C++
- CLI
- Java
- Julia
- Node.js
- Python
- Overview
- Data Ingestion
- Result Conversion
- DB API
- Relational API
- Function API
- Types API
- Expression API
- Spark API
- API Reference
- Known Python Issues
- R
- Rust
- Scala
- Swift
- Wasm
- ADBC
- ODBC
- SQL
- Introduction
- Statements
- Overview
- Alter Table
- Alter View
- Attach/Detach
- Call
- Checkpoint
- Copy
- Create Macro
- Create Schema
- Create Sequence
- Create Table
- Create View
- Create Type
- Delete
- Drop
- Export
- Insert
- Pivot
- Select
- Set/Reset
- Unpivot
- Update
- Use
- Vacuum
- Query Syntax
- SELECT
- FROM & JOIN
- WHERE
- GROUP BY
- GROUPING SETS
- HAVING
- ORDER BY
- LIMIT
- SAMPLE
- UNNEST
- WITH
- WINDOW
- QUALIFY
- VALUES
- FILTER
- Set Operations
- Data Types
- Overview
- Bitstring
- Blob
- Boolean
- Date
- Enum
- Interval
- List
- Map
- NULL Values
- Numeric
- Struct
- Text
- Time
- Timestamp
- Time Zones
- Union
- Expressions
- Functions
- Overview
- Bitstring Functions
- Blob Functions
- Date Format Functions
- Date Functions
- Date Part Functions
- Enum Functions
- Interval Functions
- Nested Functions
- Numeric Functions
- Pattern Matching
- Text Functions
- Time Functions
- Timestamp Functions
- Timestamp with Time Zone Functions
- Utility Functions
- Aggregates
- Configuration
- Constraints
- Indexes
- Information Schema
- Metadata Functions
- Pragmas
- Rules for Case Sensitivity
- Samples
- Window Functions
- Extensions
- Sitemap
- Why DuckDB
- Media
- FAQ
- Code of Conduct
- Live Demo
The QUALIFY
clause is used to filter the results of WINDOW
functions. This filtering of results is similar to how a HAVING
clause filters the results of aggregate functions applied based on the GROUP BY
clause.
The QUALIFY
clause avoids the need for a subquery or WITH
clause to perform this filtering (much like HAVING
avoids a subquery). An example using a WITH
clause instead of QUALIFY
is included below the QUALIFY
examples.
Note that this is filtering based on WINDOW
functions, not necessarily based on the WINDOW
clause. The WINDOW
clause is optional and can be used to simplify the creation of multiple WINDOW
function expressions.
The position of where to specify a QUALIFY
clause is following the WINDOW
clause in a SELECT
statement (WINDOW
does not need to be specified), and before the ORDER BY
.
Examples
Each of the following examples produce the same output, located below.
-- Filter based on a WINDOW function defined in the QUALIFY clause
SELECT
schema_name,
function_name,
-- In this example the function_rank column in the select clause is for reference
row_number() OVER (PARTITION BY schema_name ORDER BY function_name) AS function_rank
FROM duckdb_functions()
QUALIFY
row_number() OVER (PARTITION BY schema_name ORDER BY function_name) < 3;
-- Filter based on a WINDOW function defined in the SELECT clause
SELECT
schema_name,
function_name,
row_number() OVER (PARTITION BY schema_name ORDER BY function_name) AS function_rank
FROM duckdb_functions()
QUALIFY
function_rank < 3;
-- Filter based on a WINDOW function defined in the QUALIFY clause, but using the WINDOW clause
SELECT
schema_name,
function_name,
-- In this example the function_rank column in the select clause is for reference
row_number() OVER my_window AS function_rank
FROM duckdb_functions()
WINDOW
my_window AS (PARTITION BY schema_name ORDER BY function_name)
QUALIFY
row_number() OVER my_window < 3;
-- Filter based on a WINDOW function defined in the SELECT clause, but using the WINDOW clause
SELECT
schema_name,
function_name,
row_number() OVER my_window AS function_rank
FROM duckdb_functions()
WINDOW
my_window AS (PARTITION BY schema_name ORDER BY function_name)
QUALIFY
function_rank < 3;
-- Equivalent query based on a WITH clause (without QUALIFY clause)
WITH ranked_functions AS (
SELECT
schema_name,
function_name,
row_number() OVER (PARTITION BY schema_name ORDER BY function_name) AS function_rank
FROM duckdb_functions()
)
SELECT
*
FROM ranked_functions
WHERE
function_rank < 3;
schema_name | function_name | function_rank |
---|---|---|
main | !__postfix | 1 |
main | !~~ | 2 |
pg_catalog | col_description | 1 |
pg_catalog | format_pg_type | 2 |