INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Need to find a maximum or minimum value?

Need to find a maximum or minimum value?

(OP)
Is there a quick way of getting the maximum or minimum of some columns?
ie. say I want to select the maximum of two columns I can write it as:

CODE

SELECT (CASE WHEN COL1>COL2 THEN COL1 ELSE COL2 END) AS RESULT
FROM etc...

Obviously the MAX function doesn't do this, ie

CODE

SELECT MAX(COL1,COL2) AS RESULT ...

wont work. Using CASE WHEN is OK when there's only two columns but when there's more or a combination of maximum and minimum it becomes a real pain.

Sorry if this is real beginner stuff but any help would be appreciated!

RE: Need to find a maximum or minimum value?

You could union the values of the columns, then select the max value, then go back and retrieve the base row.  Whether this is quick or easy remains to be seen.

select t3.key, t3.cola, t3.colb, t3.colc,...
from tablea t3,
   (select max(t1.value)
    from
      (Select colavalue
       from tablea
       union
       Select colbvalue
       from tablea
       union
       Select  colcvalue
       from tablea) t1(value)
     )  t2.(mvalue)
where
t3.cola = t2.mvalue
OR
t3.colb = t2.mvalue
OR
t3.colc = t2.mvalue;

RE: Need to find a maximum or minimum value?

I don't think Teradata has a magic function that works on a single row (MIN() and MAX() are GROUP BY functions), but assuming your list of columns is reasonably small, you can simulate the function in a CASE statement that's not too nasty.

Let's assume you have 4 commensurable fields, col1, col2, col3, and col4.  To compute the maximum of the four on any row, you would code:

CASE
  WHEN col1 > col2 AND col1 > col3 AND col1 > col4 THEN col1
  WHEN col2 > col3 AND col2 > col4 THEN col2
  WHEN col3 > col4 THEN col3
  ELSE col4
END     AS max_col_value

Note that the columns in this example maintain their order, and that each WHEN sub-clause has each inequality dominated (some might use the expression "majorized") by the first term.  This is the generalizing principle, and suggests that you will always have as many WHEN- and ELSE- sub-clauses as you have fields to compare.  So if you were finding the maximum of 6 fields, you would anticipate coding 6 lines within your CASE statement, including the ELSE sub-clause.

To compute minimum instead of maximum, simply reverse each inequality, and leave everything else identically as shown above.

Kevin Gillette

RE: Need to find a maximum or minimum value?

select  case when col1 > col2 then col1 else col2 end as max1,
    case when col3 > max1 then col3 else max1 end as max_col

if you have 4 colums, you need to get max1(result from col1 and col2) and max2 (result form col3 and col4). Get max_col from max1 and max2.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close