# 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.

Source: https://docs.querylab.io/bigquery/qualify/

Updated: 2026-10-14

`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**

```sql
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 (3 rows):

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

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**

```sql
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 (3 rows):

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

Querylab.io spots this subquery around `ROW_NUMBER()`, `RANK()` or `DENSE_RANK()` and has a quick fix that converts it to `QUALIFY` ([code quality](https://docs.querylab.io/writing-sql/code-quality/#suggest-qualify)).

## Where QUALIFY runs

BigQuery evaluates a query in this order:

1. `FROM`
2. `WHERE`
3. `GROUP BY` and aggregates
4. `HAVING`
5. `WINDOW` (window functions are computed here)
6. `QUALIFY`
7. `DISTINCT`
8. `ORDER BY`
9. `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 WHERE**

```sql
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
```

BigQuery rejects this query:

```text
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 function**

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

BigQuery rejects this query:

```text
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**

```sql
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 (1 row):

| player |
| --- |
| ana |

## 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**

```sql
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 (4 rows):

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

## 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**

```sql
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 (3 rows):

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

- `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**

```sql
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 (2 rows):

| region | product | total_units |
| --- | --- | --- |
| north | apples | 150 |
| south | pears | 200 |

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 units**

```sql
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
```

BigQuery rejects this query:

```text
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](https://docs.querylab.io/bigquery/how-queries-are-billed/) and [Window functions](https://docs.querylab.io/bigquery/window-functions/).

Keep the latest row per key with QUALIFY. Open the playground at https://app.querylab.io?playground=true&utm_source=docs&utm_medium=concepts to run it. It is the real app on public BigQuery datasets and needs no sign-in.

## Sources

Based on the BigQuery documentation by Google, CC BY 4.0, rewritten with examples checked against BigQuery:

- [Query syntax, QUALIFY clause](https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#qualify_clause)

- [Window function calls](https://cloud.google.com/bigquery/docs/reference/standard-sql/window-function-calls)