Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2013
    Posts
    11

    Unanswered: Max value in 3 fields

    Hi,

    I have 3 separate fields that i need to join Field1,Field2,Field3;and should find if field1 or field2 is greater than field3.

    item field1 field2 field3
    ---------------------------
    1 100 200 150
    2 200 400 50
    3 250 150 80
    4 80 100 200

    result
    ------

    item max-field
    --------------
    1 field2
    2 field2
    3 field1
    4 need not be displayed

    What kind of condition should i be using to get the result?
    Please help.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Consider using the searched case expression:
    Code:
    case 
     when field1 > field3 then field1 
     ...
    end
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    If both of field1 and field2 were less than field3,
    do you want
    (1) to display the string 'need not be displayed'?
    or
    (2) to remove the row from the result?


    If you want (1) to display the string 'need not be displayed'.

    Example 1:
    Code:
    SELECT item
         , CASE
           WHEN field1 > MAX(field2 , field3) THEN
                'field1'
           WHEN field2 > field3               THEN
                'field2'
           ELSE 'need not be displayed'
           END  AS "max-field"
     FROM  joined_result
    ;

    If you want (2) to remove the row from the result.

    Example 2:
    Code:
    SELECT item
         , CASE
           WHEN field1 > field2 THEN
                'field1'
           ELSE 'field2'
           END  AS "max-field"
     FROM  joined_result
     WHERE MAX(field1 , field2) > field3
    ;

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    n_i,

    If you asked "when field1 > field3 then field1" at first,
    item 2 would return "field1" instead of "field2".

    Code:
    item field1 field2 field3
    ---- ------ ------ ------
    1    100    200    150
    2    200    400     50
    3    250    150     80
    4     80    100    200

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by tonkuma View Post
    n_i,

    If you asked "when field1 > field3 then field1" at first,
    item 2 would return "field1" instead of "field2".
    True, but the GREATEST() might fix that.
    ---
    "It does not work" is not a valid problem statement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •