# MAX/MIN, Scalar vs Aggregate function and NULLs #db2fori #ibmi

This is a note on a surprising difference and kind of a follow-up on a RPGPGM.COM article (so go read it first and put the site in your favorite!). I already commented on it there, but thought it would be worth a small post.

Try to guess the result for the following statement, then let it run and observe

```/*
* (max_row of max_col) = (max_col of max_row) ?
*/

SELECT
max(max(a),max(b),max(c)) as max_colmax
, max(max(a,b,c)) max_rowmax
FROM (
VALUES (1,2,0), (3,0,1), (1,8,3)
) as inpx( a, b, c )
;

/*
* funky NULLs ...
* (max_row of max_col) <> (max_col of max_row)
*/

SELECT
max(max(a),max(b),max(c)) as max_colmax
, max(max(a,b,c)) max_rowmax
FROM (
VALUES (1,NULL,0), (3,4,1), (NULL,8,3)
) as inpx( a, b, c )
;
```

I went with `MAX()` in this example, but the same goes for `MIN()`. More info in the doc: https://www.ibm.com/docs/en/i/7.4?topic=sf-max and https://www.ibm.com/docs/en/i/7.4?topic=functions-max.

Now you may want to write your query with `GREATEST()` and `LEAST()`for the scalar function instead, a bit less misleading IMHO.