---
title: "QUALIFY"
description: "QUALIFY filters rows based on the result of a window function, letting you write conditions like 'keep only the top-ranked row per group' without wrapping the query in a subquery."
canonical: "https://motherduck.com/glossary/qualify/"
related:
  - title: "Window functions | MotherDuck Docs"
    url: "https://motherduck.com/docs/sql-reference/duckdb-sql-reference/window-functions/"
  - title: "DuckLake Architecture Deep Dive"
    url: "https://motherduck.com/blog/ducklake-architecture-deep-dive/"
  - title: "SQL Golf: 5 SQL Tricks You Should (Probably) Never Use"
    url: "https://motherduck.com/blog/its-a-sql-golf-quackmas/"
gated_asset:
  title: "DuckLake: The Lakehouse Table Format"
  url: "https://motherduck.com/lp/ducklake-lakehouse-table-format-book-full/"
---

# QUALIFY

> QUALIFY filters rows based on the result of a window function, letting you write conditions like 'keep only the top-ranked row per group' without wrapping the query in a subquery.

## 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.

```sql
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:
```sql
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;
```

<glossary-callout guide="duckdb-cheatsheet-full" />

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

```sql
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.

```sql
-- 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.