- Installation
- Documentation
- Overview
- Connect
- Data Import
- Overview
- CSV Files
- JSON Files
- Multiple Files
- Parquet Files
- Partitioning
- Appender
- INSERT Statements
- Client APIs
- Overview
- C
- Overview
- Startup
- Configuration
- Query
- Data Chunks
- Values
- Types
- Prepared Statements
- Appender
- Table Functions
- Replacement Scans
- API Reference
- C++
- CLI
- Go
- Java
- Julia
- Node.js
- Python
- Overview
- Data Ingestion
- Conversion between DuckDB and Python
- DB API
- Relational API
- Function API
- Types API
- Expression API
- Spark API
- API Reference
- Known Python Issues
- R
- Rust
- Swift
- Wasm
- ADBC
- ODBC
- Configuration
- SQL
- Introduction
- Statements
- Overview
- ALTER TABLE
- ALTER VIEW
- ATTACH/DETACH
- CALL
- CHECKPOINT
- COMMENT ON
- COPY
- CREATE INDEX
- CREATE MACRO
- CREATE SCHEMA
- CREATE SECRET
- CREATE SEQUENCE
- CREATE TABLE
- CREATE VIEW
- CREATE TYPE
- DELETE
- DROP
- EXPORT/IMPORT DATABASE
- INSERT
- PIVOT
- Profiling
- SELECT
- SET/RESET
- Transaction Management
- UNPIVOT
- UPDATE
- USE
- VACUUM
- Query Syntax
- SELECT
- FROM & JOIN
- WHERE
- GROUP BY
- GROUPING SETS
- HAVING
- ORDER BY
- LIMIT
- SAMPLE
- Unnesting
- WITH
- WINDOW
- QUALIFY
- VALUES
- FILTER
- Set Operations
- Prepared Statements
- Data Types
- Overview
- Array
- Bitstring
- Blob
- Boolean
- Date
- Enum
- Interval
- List
- Literal Types
- Map
- NULL Values
- Numeric
- Struct
- Text
- Time
- Timestamp
- Time Zones
- Union
- Typecasting
- Expressions
- Overview
- CASE Statement
- Casting
- Collations
- Comparisons
- IN Operator
- Logical Operators
- Star Expression
- Subqueries
- Functions
- Overview
- Bitstring Functions
- Blob Functions
- Date Format Functions
- Date Functions
- Date Part Functions
- Enum Functions
- Interval Functions
- Lambda Functions
- Nested Functions
- Numeric Functions
- Pattern Matching
- Regular Expressions
- Text Functions
- Time Functions
- Timestamp Functions
- Timestamp with Time Zone Functions
- Utility Functions
- Aggregate Functions
- Constraints
- Indexes
- Information Schema
- Metadata Functions
- Keywords and Identifiers
- Samples
- Window Functions
- Extensions
- Overview
- Official Extensions
- Working with Extensions
- Versioning of Extensions
- Arrow
- AutoComplete
- AWS
- Azure
- Excel
- Full Text Search
- httpfs (HTTP and S3)
- Iceberg
- ICU
- inet
- jemalloc
- JSON
- MySQL
- PostgreSQL
- Spatial
- SQLite
- Substrait
- TPC-DS
- TPC-H
- Guides
- Overview
- Data Import & Export
- Overview
- CSV Import
- CSV Export
- Parquet Import
- Parquet Export
- Querying Parquet Files
- HTTP Parquet Import
- S3 Parquet Import
- S3 Parquet Export
- S3 Iceberg Import
- S3 Express One
- GCS Import
- Cloudflare R2 Import
- JSON Import
- JSON Export
- Excel Import
- Excel Export
- MySQL Import
- PostgreSQL Import
- SQLite Import
- Directly Reading Files
- Performance
- Overview
- Schema
- Indexing
- Environment
- File Formats
- How to Tune Workloads
- My Workload Is Slow
- Benchmarks
- Meta Queries
- Describe Table
- EXPLAIN: Inspect Query Plans
- EXPLAIN ANALYZE: Profile Queries
- List Tables
- Summarize
- DuckDB Environment
- ODBC
- Python
- Installation
- Executing SQL
- Jupyter Notebooks
- SQL on Pandas
- Import from Pandas
- Export to Pandas
- Import from Numpy
- Export to Numpy
- SQL on Arrow
- Import from Arrow
- Export to Arrow
- Relational API on Pandas
- Multiple Python Threads
- Integration with Ibis
- Integration with Polars
- Using fsspec Filesystems
- SQL Features
- SQL Editors
- Data Viewers
- Snippets
- Development
- DuckDB Repositories
- Testing
- Overview
- sqllogictest Introduction
- Writing Tests
- Debugging
- Result Verification
- Persistent Testing
- Loops
- Multiple Connections
- Catch
- Profiling
- Release Calendar
- Building
- Benchmark Suite
- Internals
- 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 |
Syntax
About this page
Last modified: 2024-04-18