How to Use QUALIFY in BigQuery
Last updated August 30, 2026 · By the SaturnSQL team
QUALIFY filters on window-function results the way HAVING filters on aggregates, so "latest row per key" becomes one clause instead of a wrapped subquery: QUALIFY ROW_NUMBER() OVER (PARTITION BY key ORDER BY ts DESC) = 1.
SELECT *
FROM orders
QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) = 1;Where it runs
QUALIFY is evaluated after WHERE, GROUP BY, HAVING, and the window functions themselves, which is exactly why it works: by the time it filters, the window values exist. Without it you would have to wrap the query in a subselect just to reference the window column. If BigQuery rejects a query whose only filter is QUALIFY, add WHERE TRUE - the dialect expects a WHERE, GROUP BY, or HAVING clause alongside it.
Top N per group
SELECT category, product, revenue
FROM product_revenue
WHERE TRUE
QUALIFY RANK() OVER (PARTITION BY category ORDER BY revenue DESC) <= 3
ORDER BY category, revenue DESC;Filter on a window column from SELECT
The window function can live in the SELECT list instead of the QUALIFY clause itself; QUALIFY can then reference its alias directly.
SELECT
customer_id,
created_at,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at) AS order_rank
FROM orders
WHERE TRUE
QUALIFY order_rank <= 2;Run this in SaturnSQL
SaturnSQL is a browser-based SQL editor for teams: shared query library, schema-aware autocomplete, and scheduled exports to Google Sheets and Slack.
Try it free