Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2013
    Posts
    2

    Unanswered: Fetching maximum date from multiple columns

    Hi,

    I have a table with below structure.

    Code:
    SNbr  DATE1            DATE2           DATE3
    1       20120101       20120505       NULL
    2       20100728       20090314       20070112
    For each row I want to select only one column from DATE1, DATE2 & DATE3 based on whichever is maximum. For instance the Final results should look like.

    Code:
    SNbr     MYDATE
    1          20120505
    2          20100728
    Please let me know how to achieve such results.

  2. #2
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    Code:
    SELECT SNbr, MAX(DATE1, DATE2, DATE3) AS MYDATE
    FROM your_table
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    MAX scalar function (GREATEST is a synonym) returns NULL if an argument was NULL.
    Note: different handling of NULL from MAX aggregate function.

    Example 1:
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     sample_data
    ( s_nbr , date1 , date2 , date3 ) AS (
    VALUES
      (  1 , 20120101 , 20120505 , NULL     )
    , (  2 , 20100728 , 20090314 , 20070112 )
    , (  3 , NULL     , 20090314 , 20070112 )
    , (  4 , NULL     , 20070112 , 20090314 )
    , (  5 , 20100728 , NULL     , 20090314 )
    , (  6 , 20090314 , NULL     , 20100728 )
    , (  7 , 20120505 , 20120101 , NULL     )
    , (  8 , 20090314 , 20100728 , 20070112 )
    , (  9 , 20090314 , 20070112 , 20100728 )
    , ( 10 , NULL     , NULL     , NULL     )
    )
    SELECT s_nbr
         , MAX(date1, date2, date3) AS my_date_a
         , MAX(
              COALESCE(date1 , date2 , date3)
            , COALESCE(date2 , date3 , date1)
            , COALESCE(date3 , date1 , date2)
           ) AS my_date_t
     FROM  sample_data
    ;
    ------------------------------------------------------------------------------
    
    S_NBR       MY_DATE_A   MY_DATE_T  
    ----------- ----------- -----------
              1           -    20120505
              2    20100728    20100728
              3           -    20090314
              4           -    20090314
              5           -    20100728
              6           -    20100728
              7           -    20120505
              8    20100728    20100728
              9    20100728    20100728
             10           -           -
    
      10 record(s) selected.

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Another example to show the handling of NULL in MAX aggregate function.

    Example 2: No practical meaning in "GROUP BY s_nbr / 3".
    It was used only to show the NULL value(s) are ignored in MAX aggregate function.
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     sample_data
    ( s_nbr , date1 , date2 , date3 , date4 ) AS (
    VALUES
      (  1 , 20120101 , 20120505 , NULL     , 20130505 )
    , (  2 , 20100728 , 20090314 , 20070112 , NULL     )
    , (  3 , NULL     , 20090314 , 20070112 , NULL     )
    , (  4 , NULL     , 20070112 , 20090314 , NULL     )
    , (  5 , 20100728 , NULL     , 20090314 , NULL     )
    , (  6 , 20090314 , NULL     , 20100728 , NULL     )
    , (  7 , 20120505 , 20120101 , NULL     , 20090314 )
    , (  8 , 20090314 , 20100728 , 20070112 , 20070112 )
    , (  9 , 20090314 , 20070112 , 20100728 , NULL     )
    , ( 10 , NULL     , NULL     , NULL     , NULL     )
    )
    SELECT s_nbr / 3 AS s_nbr_grp
         , VARCHAR(
              VARCHAR( MIN(s_nbr) ) || ' - ' || VARCHAR( MAX(s_nbr) )
            , 14 ) AS "Range of s_nbr"
         , MAX(date1) AS max_date1
         , MAX(date2) AS max_date2
         , MAX(date3) AS max_date3
         , MAX(date4) AS max_date4
     FROM  sample_data
     GROUP BY
           s_nbr / 3
    ;
    ------------------------------------------------------------------------------
    
    S_NBR_GRP   Range of s_nbr MAX_DATE1   MAX_DATE2   MAX_DATE3   MAX_DATE4  
    ----------- -------------- ----------- ----------- ----------- -----------
              0 1 - 2             20120101    20120505    20070112    20130505
              1 3 - 5             20100728    20090314    20090314           -
              2 6 - 8             20120505    20120101    20100728    20090314
              3 9 - 10            20090314    20070112    20100728           -
    
      4 record(s) selected.

  5. #5
    Join Date
    Mar 2013
    Posts
    2
    Thank you everyone for your prompt reply.

Posting Permissions

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