How to Use LEAST and GREATEST in BigQuery

These work across columns within a row, which is the opposite of MIN and MAX, which work down a column across rows. All arguments have to share a comparable type.

SELECT order_id,
       LEAST(list_price, sale_price)    AS best_price,
       GREATEST(created_at, updated_at) AS last_touched
FROM orders;

One NULL wipes out the result

Unlike MIN and MAX, which ignore NULLs, LEAST and GREATEST return NULL as soon as any argument is NULL. Since a missing sale price is common, this catches people out often. Substitute a neutral value first: a very high one for LEAST, a very low one for GREATEST.

SELECT LEAST(list_price, IFNULL(sale_price, list_price)) AS best_price
FROM orders;

Clamping a value to a range

Nesting the two gives you a clamp, which is handy for capping a computed score or a discount.

SELECT LEAST(GREATEST(score, 0), 100) AS clamped_score
FROM results;

Do more with BigQuery

• Export BigQuery to Google Sheets on a schedule
• Send BigQuery results to Slack
• BigQuery SQL client in the browser