Results 1 to 4 of 4

Thread: max function

  1. #1
    Join Date
    Mar 2013
    Posts
    1

    Unanswered: max function

    Hi,
    I have a requirement where I have to get the max value in a particular column and if there is any column with null, the function should pick up null as the maximum value and display the word 'RUNNING'.

    I have tried the following, but seems not to work and is picking up the maximum value as max and not null as maximum.

    to_char(max(NVL(end_time,'RUNNING')),'yyyy-mm-dd HH:MI: SS')

    can someone pls help.

    Thanks.

  2. #2
    Join Date
    Jul 2011
    Location
    USA
    Posts
    80
    could you please share describe table <tablename>
    or provide us the detail about the data type of the column .

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    If datatype of end_time was TIMESTAMP,
    try the following examples.

    Example 1:
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     test_data
    ( group_id , end_time ) AS (
    VALUES
      ( 'Have NULL' , current_timestamp           )
    , ( 'Have NULL' , current_timestamp + 2 month )
    , ( 'Have NULL' , current_timestamp + 3 month )
    , ( 'Have NULL' , null                        )
    , ( 'No NULL'   , current_timestamp           )
    , ( 'No NULL'   , current_timestamp + 2 month )
    , ( 'No NULL'   , current_timestamp + 3 month )
    )
    SELECT group_id
         , VARCHAR(
              MAX( COALESCE(TO_CHAR(end_time , 'yyyy-mm-dd hh24:mi:ss') , 'RUNNING') )
            , 19
           ) AS max_end_time
     FROM  test_data
     GROUP BY
           group_id
    ;
    ------------------------------------------------------------------------------
    
    GROUP_ID  MAX_END_TIME       
    --------- -------------------
    Have NULL RUNNING            
    No NULL   2013-06-03 23:12:52
    
      2 record(s) selected.

    Example 2: If you don't stick srictly the output format
    Code:
    /* same test_data as Example 1 */
    SELECT group_id
         , MAX( COALESCE(VARCHAR(end_time) , 'RUNNING') ) AS max_end_time
     FROM  test_data
     GROUP BY
           group_id
    ;
    ------------------------------------------------------------------------------
    
    GROUP_ID  MAX_END_TIME              
    --------- --------------------------
    Have NULL RUNNING                   
    No NULL   2013-06-03-23.14.45.434000
    
      2 record(s) selected.

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Example 1 and 2 depend on collating sequence(digits are lower than alphabet).

    To check directly the existence of null value,
    try the follwing examples.

    Example 3:
    Code:
    SELECT group_id
         , CASE
           WHEN COUNT(end_time) = COUNT(*) THEN
                VARCHAR( TO_CHAR( MAX(end_time) , 'yyyy-mm-dd hh24:mi:ss' ) , 19 )
           ELSE /*
                COUNT(end_time) < COUNT(*) THEN
                */
                'RUNNING'
           END  AS max_end_time
     FROM  test_data
     GROUP BY
           group_id
    ;
    ------------------------------------------------------------------------------
    
    GROUP_ID  MAX_END_TIME       
    --------- -------------------
    Have NULL RUNNING            
    No NULL   2013-06-04 09:48:45
    
      2 record(s) selected.
    Example 4:
    Code:
    SELECT group_id
         , CASE
           WHEN COUNT(end_time) = COUNT(*) THEN
                VARCHAR( MAX(end_time) )
           ELSE /*
                COUNT(end_time) < COUNT(*) THEN
                */
                'RUNNING'
           END  AS max_end_time
     FROM  test_data
     GROUP BY
           group_id
    ;
    ------------------------------------------------------------------------------
    
    GROUP_ID  MAX_END_TIME              
    --------- --------------------------
    Have NULL RUNNING                   
    No NULL   2013-06-04-09.49.15.106000
    
      2 record(s) selected.

Tags for this Thread

Posting Permissions

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