2 Replies Latest reply on Jun 18, 2015 8:38 AM by RogerT

    highest value in a columns separated by comma

    1836026

      I've a table with data as shown below

       

                   trx_numbers                             trx_dates                          trx_ref

      01616145/01616332/016164592014-11-13/2014-11-19/2014-11-242014090001
      01616145/01616332/016164592014-11-13/2014-11-19/2014-11-242014120768

       

       

      I need a sql to fetch the highest date and corresponding for each trx_ref

      in above example: 2014090001 -- 2014-11-24  -- 01616459

        • 1. Re: highest value in a columns separated by comma
          BobDJ

          Hi,

           

          please move/repost your question in the SQL & PL/SQL forum, you will benefit more from the proper forum.

          • 2. Re: highest value in a columns separated by comma
            RogerT

            First advice would be to build something like a decent datamodel....

             

            To solve your problem with your "model" you could do (ORACLE 11.2):

             

            WITH DATA (trx_numbers,trx_dates,trx_ref)

                   AS (SELECT '01616145/01616332/01616459','2014-11-13/2014-11-19/2014-11-24',2014090001 FROM dual UNION ALL

                       SELECT '01616145/01616332/01616459','2014-11-13/2014-11-19/2014-11-24',2014120768 FROM dual)

                ,splitter AS (SELECT trx_ref

                                   , to_number(regexp_substr(trx_numbers,'[^/]+',1,LEVEL)) AS trx_number

                                   , to_date(regexp_substr(trx_dates,'[^/]+',1,LEVEL),'YYYY-MM-DD') AS trx_date

                                FROM DATA

                               CONNECT BY LEVEL <= (regexp_count(trx_numbers,'/') + 1)

                                   AND trx_ref = PRIOR trx_ref

                                   AND PRIOR sys_guid() IS NOT NULL)

            SELECT trx_ref

                 , MAX(trx_date)   KEEP (DENSE_RANK FIRST ORDER BY trx_date DESC) as trx_date

                 , MAX(trx_number) KEEP (DENSE_RANK FIRST ORDER BY trx_date DESC) as trx_number

              FROM splitter

            group by trx_ref

            /

             

            hth

            1 person found this helpful