Sometimes I find something that I think is cool, it may be something small, like this, but I feel it is worth sharing.
I was asked about how to find the minimum value from multiple columns. SQL offers us the MIN function for returning the smallest value from a column. I can use that to get the smallest value from multiple columns, and the smallest value from all of those columns.
I have a file with three fields containing the following numbers:
F1 | F2 | F3 |
5 | 11 | 17 |
222 | 100 | 365 |
456 | 52 | 1 |
All I need to do is to use the MIN and list the three fields/columns within it:
SELECT MIN(F1,F2,F3) FROM TESTFILE |
The returned results show me the minimum value within those three columns for each of the rows:
MIN 5 100 1 |
So what about getting the lowest value for all the rows. I can think of two ways to do it, but I am unsure which one would be the most efficient over a very large table/file and using many fields/columns.
SELECT MIN(MIN(F1,F2,F3)) FROM TESTFILE SELECT MIN(MIN(F1),MIN(F2),MIN(F3)) FROM TESTFILE |
Both of these give me the same result:
MIN 1 |
I can do the same looking for the largest value too using MAX:
SELECT MAX(F1,F2,F3) FROM TESTFILE |
Which return the following results:
MAX 17 365 456 |
I could go crazy here with… The largest of the small values:
SELECT MAX(MIN(F1,F2,F3)) FROM TESTFILE MAX 100 |
Or the smallest of the large values:
SELECT MIN(MAX(F1,F2,F3)) FROM TESTFILE MAX 17 |
Before I come up with more strange examples I am sure you get the idea.
If I want to use the result in a program I would code something like:
01 dcl-s Var packed(3) ; 02 exec sql SELECT MIN(MIN(F1,F2,F3)) INTO :Var FROM TESTFILE |
The order of the fields/columns is not important.
This article was written for IBM i 7.3, and should work for earlier releases too.
Another great tool for the SQL tool box, thanks for this great example.
ReplyDeletethat's really cool.
ReplyDeleteThe harder work to report where in the matrix the MIN value appears is SELECT X FROM FILE WHERE 5 IN (F1,F2,F3) . . . Yes, You can put Field names inside the parentheses. The other thing about both of these techniques is if the Minimum value appears twice in the matrix, it is reported in both places. - John Voris
ReplyDeleteReally cool Simon. Thank you.
ReplyDeleteFor the first example, wouldn't the smallest value of F2 be 11, not 100 or are these alpha fields and are sorted by the first character followed by the second character?
ReplyDeleteThank you Simon
ReplyDeleteAnd then it gets tricky to predict the way it reacts with NULLS. It is not what I expected...
ReplyDeleteHave you tried using the IFNULL to translate null to another value?
DeleteYes, or even coalesce. But as always with Null...
DeleteSELECT
max(max(a),max(b),max(c)) as max_colmax
, max(max(a,b,c)) max_rowmax
FROM ( VALUES (1,2,3), (3,0,1), (NULL,8,3) ) as inpx( a, b, c )
;
|MAX_COLMAX|MAX_ROWMAX|
|----------|----------|
| 8| 3|
Thanks Sir Loved it ðŸ˜ƒ
ReplyDeleteThanks for this guidance ❤️