Finding the minimum value across several columns

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.