GREATESTPROPAGATENULL in Tableau: the SQL equivalent, with a worked example

GREATESTPROPAGATENULL([amount], [other])

id	amount	units	score	other
1	-2.5	2	-0.8	3
2	0	0	0	4
3	1.5	3	0.25	2
4	4	4	0.5	8
5	9	5	0.8	11
6	16	6	1	14
7	null	7	null	9
8	4	8	-0.5	null

SELECT CASE WHEN amount IS NULL OR other IS NULL THEN NULL ELSE greatest(amount,other) END AS result FROM input ORDER BY id

Runs the example above.

SELECT CASE WHEN amount IS NULL OR other IS NULL THEN NULL ELSE greatest(amount,other) END AS result FROM input ORDER BY id

Written for this dialect; not run here.

SELECT CASE WHEN amount IS NULL OR other IS NULL THEN NULL ELSE greatest(amount,other) END AS result FROM input ORDER BY id

Written for this dialect; not run here.

id	amount	units	score	result
1	-2.5	2	-0.8	3
2	0	0	0	4
3	1.5	3	0.25	2
4	4	4	0.5	8
5	9	5	0.8	11
6	16	6	1	16
7	null	7	null	null
8	4	8	-0.5	null

Same rows from the calculation and from the SQL in DuckDB.

Where a naive translation goes wrong

DuckDB ordinary extrema skip null rather than propagating it.
Naive SQL
```
SELECT greatest(amount,other) AS result FROM input ORDER BY id
```
id amount units score result
1 -2.5 2 -0.8 3
2 0 0 0 4
3 1.5 3 0.25 2
4 4 4 0.5 8
5 9 5 0.8 11
6 16 6 1 16
7 null 7 null 9
8 4 8 -0.5 4
Matches the calculation
```
SELECT CASE WHEN amount IS NULL OR other IS NULL THEN NULL ELSE greatest(amount,other) END AS result FROM input ORDER BY id
```
id amount units score result
1 -2.5 2 -0.8 3
2 0 0 0 4
3 1.5 3 0.25 2
4 4 4 0.5 8
5 9 5 0.8 11
6 16 6 1 16
7 null 7 null null
8 4 8 -0.5 null

GREATESTPROPAGATENULL(number, number, ...)