Querying

QUALIFY in BigQuery

QUALIFY filters rows on the result of a window function, the way HAVING filters on an aggregate. The common use is keeping the latest row per key without a subquery.

QUALIFY filters rows by the value of a window function. It is to window functions what HAVING is to aggregates: WHERE runs before window functions are computed, so it can’t see them, and QUALIFY runs after. Postgres and MySQL don’t have it, so the usual workaround there is a subquery.

Keep the latest row per key

The most common use is deduplication: number the rows inside each group, newest first, and keep row 1.

latest order per customer

1234567891011

WITH orders AS (
  SELECT 'c1' AS customer_id, 'o1' AS order_id, TIMESTAMP '2026-01-03 10:00:00' AS ordered_at UNION ALL
  SELECT 'c1', 'o2', TIMESTAMP '2026-02-11 09:30:00' UNION ALL
  SELECT 'c2', 'o3', TIMESTAMP '2026-01-20 14:00:00' UNION ALL
  SELECT 'c2', 'o4', TIMESTAMP '2026-01-05 08:15:00' UNION ALL
  SELECT 'c3', 'o5', TIMESTAMP '2026-03-01 12:00:00'
)
SELECT customer_id, order_id, ordered_at
FROM orders
QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY ordered_at DESC) = 1
ORDER BY customer_id

Result in BigQuery

	customer_id	order_id	ordered_at
1	c1	o2	2026-02-11 09:30:00 UTC
2	c2	o3	2026-01-20 14:00:00 UTC
3	c3	o5	2026-03-01 12:00:00 UTC

3 rows

Without QUALIFY you compute the row number in a subquery and filter outside it. The result is the same, with more code and an extra rn column to leave out:

the same with a subquery

1234567891011121314

WITH orders AS (
  SELECT 'c1' AS customer_id, 'o1' AS order_id, TIMESTAMP '2026-01-03 10:00:00' AS ordered_at UNION ALL
  SELECT 'c1', 'o2', TIMESTAMP '2026-02-11 09:30:00' UNION ALL
  SELECT 'c2', 'o3', TIMESTAMP '2026-01-20 14:00:00' UNION ALL
  SELECT 'c2', 'o4', TIMESTAMP '2026-01-05 08:15:00' UNION ALL
  SELECT 'c3', 'o5', TIMESTAMP '2026-03-01 12:00:00'
)
SELECT customer_id, order_id, ordered_at
FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY ordered_at DESC) AS rn
  FROM orders
)
WHERE rn = 1
ORDER BY customer_id

Result in BigQuery

	customer_id	order_id	ordered_at
1	c1	o2	2026-02-11 09:30:00 UTC
2	c2	o3	2026-01-20 14:00:00 UTC
3	c3	o5	2026-03-01 12:00:00 UTC

3 rows

Querylab.io spots this subquery around ROW_NUMBER(), RANK() or DENSE_RANK() and has a quick fix that converts it to QUALIFY (code quality).

Where QUALIFY runs

BigQuery evaluates a query in this order:

FROM
WHERE
GROUP BY and aggregates
HAVING
WINDOW (window functions are computed here)
QUALIFY
DISTINCT
ORDER BY
LIMIT

That is why a window function in WHERE is an error: at that point it hasn’t been computed yet.

a window function in WHEREBigQuery rejects this query

1234567

WITH scores AS (
  SELECT 'ana' AS player, 90 AS points UNION ALL
  SELECT 'ben', 80
)
SELECT player
FROM scores
WHERE RANK() OVER (ORDER BY points DESC) = 1

Error in BigQuery

Analytic function not allowed in WHERE clause at [7:7]

A QUALIFY clause needs a window function somewhere, in the clause itself or in the SELECT list. Without one, BigQuery refuses it, and the condition belongs in WHERE:

QUALIFY without a window functionBigQuery rejects this query

12345678

WITH scores AS (
  SELECT 'ana' AS player, 90 AS points UNION ALL
  SELECT 'ben', 80
)
SELECT player
FROM scores
WHERE TRUE
QUALIFY points > 85

Error in BigQuery

The QUALIFY clause requires analytic function to be present at [8:9]

Older answers online say QUALIFY only works together with WHERE, GROUP BY or HAVING, and suggest adding WHERE TRUE. BigQuery no longer requires that:

QUALIFY with no WHERE

1234567

WITH scores AS (
  SELECT 'ana' AS player, 90 AS points UNION ALL
  SELECT 'ben', 80
)
SELECT player
FROM scores
QUALIFY RANK() OVER (ORDER BY points DESC) = 1

Result in BigQuery

	player
1	ana

1 row

Filtering on a SELECT alias

QUALIFY can refer to a window function’s alias from the SELECT list, so you don’t have to repeat the expression:

top two players per game

12345678910111213141516

WITH scores AS (
  SELECT 'ana' AS player, 'chess' AS game, 1820 AS rating UNION ALL
  SELECT 'ben', 'chess', 1950 UNION ALL
  SELECT 'cai', 'chess', 1700 UNION ALL
  SELECT 'dee', 'go', 2100 UNION ALL
  SELECT 'eli', 'go', 2050
)
SELECT
  game,
  player,
  rating,
  RANK() OVER (PARTITION BY game ORDER BY rating DESC) AS place
FROM scores
WHERE TRUE
QUALIFY place <= 2
ORDER BY game, place

Result in BigQuery

	game	player	rating	place
1	chess	ben	1950	1
2	chess	ana	1820	2
3	go	dee	2100	1
4	go	eli	2050	2

4 rows

ROW_NUMBER, RANK or DENSE_RANK

The three numbering functions differ only in how they treat ties, and the choice decides how many rows QUALIFY ... = 1 keeps:

two players tied on 90 points

12345678910111213

WITH scores AS (
  SELECT 'ana' AS player, 90 AS points UNION ALL
  SELECT 'ben', 90 UNION ALL
  SELECT 'cai', 85
)
SELECT
  player,
  points,
  ROW_NUMBER() OVER (ORDER BY points DESC, player) AS row_number,
  RANK() OVER (ORDER BY points DESC) AS rank,
  DENSE_RANK() OVER (ORDER BY points DESC) AS dense_rank
FROM scores
ORDER BY player

Result in BigQuery

	player	points	row_number	rank	dense_rank
1	ana	90	1	1	1
2	ben	90	2	1	1
3	cai	85	3	3	2

3 rows

ROW_NUMBER gives every row a different number, so = 1 keeps exactly one row per group. With a tie, which one is arbitrary unless the ORDER BY breaks the tie. Here player does.
RANK gives tied rows the same number and skips the next ones (1, 1, 3). = 1 keeps all tied rows.
DENSE_RANK gives tied rows the same number without gaps (1, 1, 2). <= 2 means “the top two distinct scores”.

For deduplication use ROW_NUMBER and add enough columns to the ORDER BY to make it deterministic. Otherwise two runs can keep different rows.

QUALIFY after GROUP BY

Window functions run after aggregation, so QUALIFY can rank groups. This keeps the best-selling product in each region:

best product per region

123456789101112

WITH sales AS (
  SELECT 'north' AS region, 'apples' AS product, 120 AS units UNION ALL
  SELECT 'north', 'pears', 80 UNION ALL
  SELECT 'north', 'apples', 30 UNION ALL
  SELECT 'south', 'pears', 200 UNION ALL
  SELECT 'south', 'apples', 50
)
SELECT region, product, SUM(units) AS total_units
FROM sales
GROUP BY region, product
QUALIFY RANK() OVER (PARTITION BY region ORDER BY total_units DESC) = 1
ORDER BY region

Result in BigQuery

	region	product	total_units
1	north	apples	150
2	south	pears	200

2 rows

There is a trap when the aggregate’s alias has the same name as a column. Here the alias units hides the column units, so SUM(units) inside QUALIFY means SUM(SUM(units)):

the alias units hides the column unitsBigQuery rejects this query

123456789101112

WITH sales AS (
  SELECT 'north' AS region, 'apples' AS product, 120 AS units UNION ALL
  SELECT 'north', 'pears', 80 UNION ALL
  SELECT 'north', 'apples', 30 UNION ALL
  SELECT 'south', 'pears', 200 UNION ALL
  SELECT 'south', 'apples', 50
)
SELECT region, product, SUM(units) AS units
FROM sales
GROUP BY region, product
QUALIFY RANK() OVER (PARTITION BY region ORDER BY SUM(units) DESC) = 1
ORDER BY region

Error in BigQuery

Aggregations of aggregations are not allowed at [11:55]

Give aggregates names that differ from the columns they summarize, like total_units, or order by the alias itself.

Cost

QUALIFY doesn’t change what a query reads. It filters after the window function has run over every row that passed WHERE, so the bytes billed are the same as without it. To read less, filter in WHERE first, especially on the partitioning column. See How a query is billed and Window functions.