Lesson 10.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 afterWHERE 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. Postgres and MySQL do not have it yet, so fall back to a CTE or subquery that filters on the window column.

Schema · Garden ShopTable · products9 columns · 24 rows

Table · products

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

9 columns · 24 rows

product_id intproduct_name textcategory_id intsupplier_id intprice decimalcost decimalquantity_on_hand intreorder_level intdiscontinued bool

Your task

Return the most expensive product in each category: thecategory name, the product name, and itsprice. There should be exactly one row per category.

SQL Workbench

query.sqlgarden_shop · SQL engine loading

⌘↵ to run

Expected answer