1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

How to CREATE FUNCTION to find Maximum value WITHOUT using a TABLE

Discussion in 'SQL PL/SQL' started by widiarifki, Jan 19, 2012.

  1. widiarifki

    widiarifki Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    Hello everybody, I'm newbie in oracle and still getting started with it..

    I need ur help for answering my curiosity..

    How can I CREATE FUNCTION to FIND MAXIMUM VALUE FROM SOME NUMBERS I ENTERED AS PARAMETER? WITHOUT USING TABLE?

    E.G: I ENTERED AS PARAMETER (10,20,30) AND RESULT FROM THE FUNCTION CREATED IS "30"?

    HOW CAN I?

    Please clarify my doubt. :D

    Many Thanks.
     
  2. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    Your requirement is not clear. how many parameters you are entering. fixed or variable parameters ???
     
    widiarifki likes this.
  3. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    You requirement is not clear.. Anyway, if you want to get the greated value of multiple parameter you can use GREATEST function.

    Code (SQL):

    SQL> SELECT greatest(10,20,30) FROM dual;

    GREATEST(10,20,30)
    ------------------
                    30

    SQL>

     
     
    widiarifki likes this.
  4. widiarifki

    widiarifki Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    Thanks rajavu.. I got it. It's work.:hurray
    Many Thanks...
     
  5. widiarifki

    widiarifki Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    Thanks for your response kiran.marla. I entered multiple parameters and use variable parameters.
    BTW, I've got the answer from rajavu. Use GREATEST.

    Many Thanks, :D
    Regards.
     
  6. tj.abrahamsen

    tj.abrahamsen Active Member

    Messages:
    10
    Likes Received:
    1
    Trophy Points:
    90
    Location:
    Utah
    Hey all -

    I know this question has been answered using the GREATEST function, but just in case someone drops in to look...searching for something similar...

    Some times it can in addition be wishful to find a maximum value, and then compare another value against it. One way this can be done is to use a query similar to this:

    Code (SQL):
    SELECT  x.val
            ,MAX(x.val) OVER (PARTITION BY 1) max_val
            ,ROUND(((x.val * 100) / MAX(x.val) OVER (PARTITION BY 1)), 2) perc_of_max
    FROM    (
                SELECT  10 val
                FROM    dual

                UNION ALL

                SELECT  20 val
                FROM    dual

                UNION ALL

                SELECT  30 val
                FROM    dual
            ) x
    ;

    -- It gives this result:
    VAL MAX_VAL PERC_OF_MAX
    10  30      33.33
    20  30      66.67
    30  30      100
     
    For anyone interested...

    ~ TJ