Pages

Tuesday, August 22, 2017

Finding the minimum value across several columns

minimum value of more than one column

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.

10 comments:

  1. Another great tool for the SQL tool box, thanks for this great example.

    ReplyDelete
  2. The 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

    ReplyDelete
  3. For 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?

    ReplyDelete
  4. Thank you Simon

    ReplyDelete
  5. And then it gets tricky to predict the way it reacts with NULLS. It is not what I expected...

    ReplyDelete
    Replies
    1. Have you tried using the IFNULL to translate null to another value?

      Delete
    2. Yes, or even coalesce. But as always with Null...

      SELECT
          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|

      Delete
  6. Thanks Sir Loved it 😃
    Thanks for this guidance ❤️

    ReplyDelete

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.