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.

Now You Try

Practice this concept

Marketing wants clicks capped at opens and unopened recipients prevented from becoming negative.

Available schema

marketing

Prefix tables with marketing.table_name.

send_idvalid_clicksunopened_recipients

marketing.email_sends

Column	Type
id	integer
campaign_id	integer
sent_at	timestamp with time zone
recipients	integer
opens	integer
clicks	integer
unsubscribes	integer
bounces	integer
legacy_send_group	text

query.sql

Intermediate business practice