Datawise
Practical SQL

GREATEST & LEAST in BigQuery

Constantin LunguUpdated 1 min read

Photo by NordWood Themes on Unsplash

Have you ever had to compute the biggest or smallest value across multiple columns?

If so, note that in addition to using CASE WHEN or IF, we have GREATEST and LEAST, which will do exactly that.

I've also though they reminded me of how MIN / MAX in Excel works.

They also come with the advantage that you can compare multiple values at once.

As usual, pay attention to the NULLs - if one of values is NULL, the result would be as well.

WITH input_data AS (

  SELECT 10 AS val_a, 20 AS val_b, 30 AS val_c
)

SELECT

GREATEST(val_a, val_b) AS highest,

IF(val_a>=val_b, val_a, val_b) AS also_highest,

LEAST(val_a, val_b) AS lowest,

IF(val_a <= val_b, val_a, val_b) AS also_lowest,

GREATEST(val_a, val_b, val_c) AS greatest_of_all

FROM input_data

BigQuery results: highest 20, also_highest 20, lowest 10, also_lowest 10 and greatest_of_all 30.