DISTINCT

Test tables repeat the same values many times, so you often want the list of what exists instead of every row. DISTINCT gives you that list.

How it works

DISTINCT works on the rows SELECT has already built. It compares each whole output row with the others and keeps one row of each kind.

Common mistake

SELECT DISTINCT make, test_result returns up to one row per pair, so the list is longer than the list of makes. Adding a column to a DISTINCT query changes what counts as a duplicate.

Worked example

SELECT DISTINCT data_source
FROM `learn.mot.fact_mot_test`
WHERE completed_date BETWEEN '2024-03-01' AND '2024-03-31'
ORDER BY data_source

The query reads the March 2024 tests and keeps each data_source value once.

Add a second column and the same query keeps each different pair of values.

All BigQuery lessons