SQL GREATEST

Returns the largest value out of a list of arguments — a row-by-row comparison, not an aggregate across rows.

What & Why

GREATEST(value1, value2, ...) compares several values side by side, within the same row, and returns whichever one is largest. This is easy to confuse with MAX from the Aggregate Functions series — but MAX compares one column's values across many rows, while GREATEST compares several different values within one row. They solve genuinely different problems.

See How It Works

BUSINESS QUESTION

Marketing wants clicks capped at opens and unopened recipients prevented from going below zero.

id	campaign_id	sent_at	recipients	opens	clicks	unsubscribes	bounces
201	1	2024-01-20 15:00:00+00	1200	540	180	9	24
202	2	2024-02-15 16:30:00+00	800	420	96	5	12
203	3	2024-03-18 13:00:00+00	1500	610	225	14	31
204	4	2024-04-08 14:00:00+00	0	0	0	0	0

EXAMPLE QUERY

SELECT
  id AS send_id,
  LEAST(clicks, opens) AS valid_clicks,
  GREATEST(recipients - opens, 0) AS unopened_recipients
FROM marketing.email_sends
ORDER BY send_id
LIMIT 20;

RESULT — exact output from the displayed Queryflo rows

send_id	valid_clicks	unopened_recipients
201	180	660
202	96	380
203	225	890
204	0	0

LEAST caps clicks and GREATEST prevents negative unopened recipients.

Intermediate business practice