Unit 07.04: Filtering on a window result with QUALIFY

Unit ID: SQL-M07-U05 - Estimated active time: 13-16 minutes Objective: filter on a window function's output, and know the portable alternative.

You cannot filter a window in WHERE

Window functions are computed after WHERE and GROUP BY, so this is invalid everywhere:

-- expect-error: a window function is not allowed in WHERE
SELECT order_id, ROW_NUMBER() OVER (ORDER BY order_total DESC) AS rn
FROM orders
WHERE rn <= 3;          -- ERROR

Module 2's evaluation order predicts it: rn does not exist when WHERE runs.

The portable fix: wrap it

SELECT order_id, order_total, rn
FROM (
  SELECT order_id, order_total,
         ROW_NUMBER() OVER (ORDER BY order_total DESC, order_id) AS rn
  FROM orders
) t
WHERE rn <= 3
ORDER BY rn;
-- 608 | 4996.00 | 1
-- 243 | 4991.00 | 2
-- 851 | 4987.00 | 3

The window is computed in the inner query; the outer query filters its result. This works on every engine.

The concise fix: QUALIFY

DuckDB, Snowflake, BigQuery, and Teradata support QUALIFY, which filters window results directly:

SELECT order_id, order_total
FROM orders
QUALIFY ROW_NUMBER() OVER (ORDER BY order_total DESC, order_id) <= 3
ORDER BY order_total DESC;

Same three rows, no subquery. QUALIFY is to windows what HAVING is to aggregates.

Portability warning: QUALIFY is not in PostgreSQL, MySQL, or SQL Server. This is the same trap as the alias-in-WHERE extension from Module 2 - convenient locally, broken when the query moves. Use it knowingly.

Top N per group

The pattern this enables most often:

SELECT status, order_id, order_total
FROM orders
QUALIFY ROW_NUMBER() OVER (PARTITION BY status ORDER BY order_total DESC, order_id) <= 2
ORDER BY status, order_total DESC;
-- completed | 608  | 4996.00
-- completed | 243  | 4991.00
-- pending   | 1000 | 1500.00
-- pending   |  999 | 1463.00

Two rows per status, ranked within each. The subquery form does the same thing with more lines.

Practice

Return the top 2 orders per status using the portable form, and confirm it matches the QUALIFY version above.

SELECT status, order_id, order_total
FROM (
  SELECT status, order_id, order_total,
         ROW_NUMBER() OVER (PARTITION BY status ORDER BY order_total DESC, order_id) AS rn
  FROM orders
) t
WHERE rn <= 2
ORDER BY status, order_total DESC;

Takeaway

Windows cannot be filtered in WHERE. Wrap the query for portability, or use QUALIFY where it exists - and remember it does not exist in PostgreSQL.

---

Mark complete