Lesson 11.6

Filtering Windows with QUALIFY in SQL

QUALIFY filters on a window function the way HAVING filters on an aggregate, in one clause with no subquery.

A window function like row_number() is computed after WHERE and GROUP BY run, so you cannot filter on it in WHERE. The usual fix is to wrap the query in a CTE or subquery and filter outside.

QUALIFY does it in one clause. It filters on a window function the same way HAVING filters on an aggregate, so the classic "top row per group" report needs no extra nesting.

Pattern

select category_id, product_name, price
from products
qualify row_number() over (
  partition by category_id
  order by price desc
) = 1

A DuckDB and warehouse feature

QUALIFY is supported by DuckDB (which powers SQLShed), plus BigQuery, Snowflake, and others. PostgreSQL and MySQL don't support it, so fall back to a CTE or subquery that filters on the window column.

Schema · Garden Shop Tables · products, categories 2 tables

Table · products

One row per product, with price, cost, and inventory levels.

9 columns · 24 rows

product_id int product_name text category_id int supplier_id int price decimal cost decimal quantity_on_hand int reorder_level int discontinued bool

Table · categories

Lookup table of product categories.

2 columns · 8 rows

category_id int category_name text

Your task

Return the most expensive product in each category: the category name, the product name, and its price. There should be exactly one row per category, sorted by category name.