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
LEAST()for the scalar function instead, a bit less misleading IMHO.