Docs/Querying/DISTINCT
Querying

DISTINCT in BigQuery

SELECT DISTINCT treats NULLs as equal, accepts arrays and structs but refuses JSON and GEOGRAPHY, and bills the same bytes as a plain SELECT. BigQuery has no DISTINCT ON and no COUNT(DISTINCT a, b).

Practice this in a free lesson on learn.querylab.io: DISTINCT.

SELECT DISTINCT returns each different combination of the selected columns once. In BigQuery two NULLs count as the same value, arrays and structs can be compared but JSON and GEOGRAPHY can’t, and the query reads the same bytes as it would without DISTINCT. Two forms from other databases don’t exist here: DISTINCT ON (...) and COUNT(DISTINCT a, b).

NULLs are one value

ben has two rows with a NULL country, and they collapse into one:

DISTINCT on two columnsOpen in Querylab.io
1234567
WITH visits AS (
  SELECT * FROM UNNEST([
    STRUCT('ana' AS customer, 'de' AS country), ('ana', 'de'),
    ('ben', CAST(NULL AS STRING)), ('ben', NULL), ('cy', 'fr')
  ])
)
SELECT DISTINCT customer, country FROM visits ORDER BY customer
Result in BigQuery
Rowcustomercountry
1anade
2bennull
3cyfr
3 rows

COUNT(DISTINCT x) behaves differently, because aggregates skip NULLs. The same data gives two different answers:

COUNT(DISTINCT) against counting DISTINCT rowsOpen in Querylab.io
1234567
WITH visits AS (
  SELECT * FROM UNNEST(['de', 'de', 'fr', NULL]) AS country
)
SELECT
  COUNT(DISTINCT country) AS count_distinct,
  (SELECT COUNT(*) FROM (SELECT DISTINCT country FROM visits)) AS distinct_rows
FROM visits
Result in BigQuery
Rowcount_distinctdistinct_rows
123
1 row

DISTINCT doesn’t reduce the bill

BigQuery bills the columns it reads. DISTINCT only changes what happens after the read, so both of these process 1,332,943 bytes:

plain SELECT
1
SELECT word FROM `bigquery-public-data.samples.shakespeare`
BigQuery dry run

This query will process 1.3 MiB when run.

SELECT DISTINCT
1
SELECT DISTINCT word FROM `bigquery-public-data.samples.shakespeare`
BigQuery dry run

This query will process 1.3 MiB when run.

DISTINCT does add a shuffle, which uses slot time. On a large table with many different values, APPROX_COUNT_DISTINCT is cheaper in slots than COUNT(DISTINCT x) if you can accept a small error.

Which types can be DISTINCT

Arrays and structs work in SELECT DISTINCT and GROUP BY. Arrays are compared in order, so ['a', 'b'] and ['b', 'a'] stay separate rows:

DISTINCT over an array columnOpen in Querylab.io
123456
WITH orders AS (
  SELECT 1 AS id, ['a', 'b'] AS tags
  UNION ALL SELECT 1, ['a', 'b']
  UNION ALL SELECT 1, ['b', 'a']
)
SELECT DISTINCT id, tags FROM orders ORDER BY id, tags[OFFSET(0)]
Result in BigQuery
Rowidtags
11[a, b]
21[b, a]
2 rows
DISTINCT over a struct columnOpen in Querylab.io
12345
WITH orders AS (
  SELECT STRUCT('de' AS country, 10115 AS zip) AS address
  UNION ALL SELECT STRUCT('de', 10115)
)
SELECT DISTINCT address FROM orders
Result in BigQuery
Rowaddress
1{country: de, zip: 10115}
1 row

GROUP BY accepts them too:

GROUP BY an arrayOpen in Querylab.io
12
WITH orders AS (SELECT ['a', 'b'] AS tags UNION ALL SELECT ['a', 'b'])
SELECT tags, COUNT(*) AS orders FROM orders GROUP BY tags
Result in BigQuery
Rowtagsorders
1[a, b]2
1 row

Aggregate functions are stricter. COUNT(DISTINCT ...) refuses both:

COUNT(DISTINCT) on an arrayBigQuery rejects this query
12
WITH orders AS (SELECT ['a', 'b'] AS tags UNION ALL SELECT ['a', 'b'])
SELECT COUNT(DISTINCT tags) AS different_tag_lists FROM orders
Error in BigQuery

Aggregate functions with DISTINCT cannot be used with arguments of type ARRAY

COUNT(DISTINCT) on a structBigQuery rejects this query
12
WITH orders AS (SELECT STRUCT('de' AS country) AS address)
SELECT COUNT(DISTINCT address) AS addresses FROM orders
Error in BigQuery

Aggregate functions with DISTINCT cannot be used with arguments of type STRUCT

JSON and GEOGRAPHY can’t be compared at all:

DISTINCT on JSONBigQuery rejects this query
12
WITH events AS (SELECT PARSE_JSON('{"a": 1}') AS payload)
SELECT DISTINCT payload FROM events
Error in BigQuery

Column payload of type JSON cannot be used in SELECT DISTINCT at [2:17]

DISTINCT on GEOGRAPHYBigQuery rejects this query
12
WITH stores AS (SELECT ST_GEOGPOINT(13.4, 52.5) AS location)
SELECT DISTINCT location FROM stores
Error in BigQuery

Column location of type GEOGRAPHY cannot be used in SELECT DISTINCT at [2:17]

Convert them first: TO_JSON_STRING(payload) for JSON, ST_ASTEXT(location) for GEOGRAPHY. A SELECT DISTINCT * fails the same way if any column in the table has one of these types. The same goes for an array of JSON. See Column of type ARRAY cannot be used in SELECT DISTINCT.

What counts as equal

Strings compare byte by byte, so case matters unless you lower it or use a collation:

three spellings of one nameOpen in Querylab.io
123456
WITH visits AS (SELECT * FROM UNNEST(['Ana', 'ana', 'ANA']) AS name)
SELECT
  COUNT(DISTINCT name) AS as_stored,
  COUNT(DISTINCT LOWER(name)) AS lowered,
  COUNT(DISTINCT COLLATE(name, 'und:ci')) AS collated
FROM visits
Result in BigQuery
Rowas_storedloweredcollated
1311
1 row

For floats, 0.0 and -0.0 are the same value and all NaNs are one value, so these four inputs give two:

signed zero and NaNOpen in Querylab.io
1234
WITH readings AS (
  SELECT * FROM UNNEST([0.0, -0.0, CAST('NaN' AS FLOAT64), CAST('NaN' AS FLOAT64)]) AS value
)
SELECT COUNT(DISTINCT value) AS distinct_values FROM readings
Result in BigQuery
Rowdistinct_values
12
1 row

Counting pairs of columns

COUNT(DISTINCT a, b) works in MySQL. BigQuery’s COUNT takes one argument:

COUNT(DISTINCT) on two columnsBigQuery rejects this query
12
WITH visits AS (SELECT 'ana' AS customer, 'de' AS country)
SELECT COUNT(DISTINCT customer, country) AS pairs FROM visits
Error in BigQuery

No matching signature for aggregate function COUNT Argument types: STRING, STRING Signature: COUNT(T1) Signature accepts at most 1 argument, found 2 arguments at [2:8]

The common fix, CONCAT, is wrong twice. 'ab' + 'c' and 'a' + 'bc' become the same string, and a NULL in either column makes the whole result NULL, which the count then skips. TO_JSON_STRING over a struct keeps the columns apart and keeps the NULL:

CONCAT against TO_JSON_STRINGOpen in Querylab.io
12345678910
WITH visits AS (
  SELECT * FROM UNNEST([
    STRUCT('ab' AS first_part, 'c' AS second_part),
    ('a', 'bc'), ('z', NULL)
  ])
)
SELECT
  COUNT(DISTINCT CONCAT(first_part, second_part)) AS with_concat,
  COUNT(DISTINCT TO_JSON_STRING(STRUCT(first_part, second_part))) AS with_json
FROM visits
Result in BigQuery
Rowwith_concatwith_json
113
1 row

The three pairs count as 1 with CONCAT and 3 with TO_JSON_STRING. Counting rows from a SELECT DISTINCT a, b subquery gives the same answer as the JSON form.

No DISTINCT ON

Postgres has DISTINCT ON (customer) for “one row per customer”. BigQuery doesn’t:

DISTINCT ONBigQuery rejects this query
12
WITH orders AS (SELECT 'ana' AS customer, 30 AS amount)
SELECT DISTINCT ON (customer) customer, amount FROM orders
Error in BigQuery

Syntax error: Unexpected keyword ON at [2:17]

Use QUALIFY with ROW_NUMBER. It says which row wins, which DISTINCT can’t:

latest order per customerOpen in Querylab.io
12345678910
WITH orders AS (
  SELECT * FROM UNNEST([
    STRUCT('ana' AS customer, DATE '2026-01-05' AS placed, 30 AS amount),
    ('ana', DATE '2026-02-01', 25), ('ben', DATE '2026-01-20', 15)
  ])
)
SELECT customer, placed, amount
FROM orders
QUALIFY ROW_NUMBER() OVER (PARTITION BY customer ORDER BY placed DESC) = 1
ORDER BY customer
Result in BigQuery
Rowcustomerplacedamount
1ana2026-02-0125
2ben2026-01-2015
2 rows

DISTINCT with ORDER BY

After SELECT DISTINCT, only the selected columns exist, so you can’t sort by another one:

ORDER BY a column that isn't selectedBigQuery rejects this query
12
WITH orders AS (SELECT 'ana' AS customer, 30 AS amount)
SELECT DISTINCT customer FROM orders ORDER BY amount
Error in BigQuery

ORDER BY clause expression references column amount which is not visible after SELECT DISTINCT at [2:47]

Inside an aggregate the rule is similar. With DISTINCT, ORDER BY can only use the aggregated value itself:

STRING_AGG(DISTINCT ... ORDER BY)BigQuery rejects this query
1234
WITH visits AS (
  SELECT * FROM UNNEST([STRUCT('fr' AS country, 2 AS rank), ('de', 1)])
)
SELECT STRING_AGG(DISTINCT country ORDER BY rank) AS countries FROM visits
Error in BigQuery

An aggregate function that has both DISTINCT and ORDER BY arguments can only ORDER BY expressions that are arguments to the function at [4:45]

ARRAY_AGG(DISTINCT x) fails on a NULL input, because BigQuery can’t return an array that holds a NULL:

ARRAY_AGG(DISTINCT) with a NULLBigQuery rejects this query
12
WITH visits AS (SELECT * FROM UNNEST(['de', 'de', NULL]) AS country)
SELECT ARRAY_AGG(DISTINCT country) AS countries FROM visits
Error in BigQuery

Array cannot have a null element; error in writing field countries

IGNORE NULLS fixes that, and ORDER BY x makes the result stable:

IGNORE NULLS and ORDER BY in the aggregateOpen in Querylab.io
12
WITH visits AS (SELECT * FROM UNNEST(['fr', 'de', 'de', NULL]) AS country)
SELECT ARRAY_AGG(DISTINCT country IGNORE NULLS ORDER BY country) AS countries FROM visits
Result in BigQuery
Rowcountries
1[de, fr]
1 row

DISTINCT in a window

COUNT(DISTINCT x) OVER (PARTITION BY ...) is allowed:

distinct customers per countryOpen in Querylab.io
12345678
WITH visits AS (
  SELECT * FROM UNNEST([
    STRUCT('de' AS country, 'ana' AS customer), ('de', 'ana'), ('de', 'ben'), ('fr', 'cy')
  ])
)
SELECT DISTINCT country, COUNT(DISTINCT customer) OVER (PARTITION BY country) AS customers
FROM visits
ORDER BY country
Result in BigQuery
Rowcountrycustomers
1de2
2fr1
2 rows

A running distinct count isn’t, because the window can’t have an ORDER BY:

running distinct countBigQuery rejects this query
12345
WITH visits AS (
  SELECT * FROM UNNEST([STRUCT(1 AS day, 'ana' AS customer), (2, 'ben')])
)
SELECT day, COUNT(DISTINCT customer) OVER (ORDER BY day) AS customers_so_far
FROM visits
Error in BigQuery

Window ORDER BY is not allowed if DISTINCT is specified at [4:44]

For a running count, mark each value’s first appearance and sum those marks over the window. Window functions covers the frames.

UNION needs an explicit DISTINCT or ALL in BigQuery. See set operations.

Open the playground. It is the real app on public datasets and needs no sign-in.