3 Replies Latest reply: Feb 20, 2013 7:36 AM by NSK2KSN RSS

    Need output as below

    NSK2KSN
      with t1 
      as 
      (
      select 10 num,  'a' ch,   1000 num2 from dual
      union all
      select 10, 'b',  1234 from dual
      union all
      select 10, 'c',  3000  from dual
      union all 
      select 20,'d',400  from dual
      union all 
      select 20,'e',23 from dual
      union all 
      select 20,'f',600  from dual
      )
      Select num, ch, num2 from t1
      
      gives below output
      
      10     1000     a
      10     1234 b
      10     3000     c
      20     400     d
      20     23 e
      20     600     f
      
      SELECT num,  LISTAGG(ch, ',') WITHIN GROUP (ORDER BY num) AS mid_val
      FROM   t1
      GROUP BY num;
      
      gives below output
      
      10 a,b,c
      20 d,e,f
      
      but need below output
      
      10 a,b,c 1234
      20 d,e,f 23
      
      I want middle record as output for every num.
      
      and I have used this query
      
      SELECT num, mid_val, num2
        FROM (SELECT t2.num,
                     mid_val,
                     t2.num2,
                     ROW_NUMBER () OVER (PARTITION BY t2.num ORDER BY t2.num) rn
                FROM (  SELECT num,
                               LISTAGG (ch, ',') WITHIN GROUP (ORDER BY num)
                                  AS mid_val
                          FROM t2
                      GROUP BY num) t21,
                     t2
               WHERE t2.num = t21.num)
      WHERE rn = 2;
      
      
      any simple queries to achieve such requirement?
        • 1. Re: Need output as below
          BluShadow
          Well, depends what you mean by "middle"... What if there are more than 3 rows per group?

          Something along these lines perhaps...
          SQL> ed
          Wrote file afiedt.buf
          
            1  with t1 as (select 10 num,  'a' ch, 1000 num2 from dual union all
            2              select 10, 'b',  1234 from dual union all
            3              select 10, 'c',  3000  from dual union all
            4              select 20,'d',400  from dual union all
            5              select 20,'e',23 from dual union all
            6              select 20,'f',600  from dual)
            7  --
            8  select num
            9        ,listagg(ch, ',') within group (order by num) AS mid_val
           10        ,max(num2) as num2
           11  from   (select num, ch
           12                ,case when row_number() over (partition by num order by ch) =
           13                           round(count(*) over (partition by num)/2)
           14                      then num2
           15                 else null end as num2
           16          from t1
           17         )
           18* group by num
          SQL> /
          
                 NUM MID_VAL          NUM2
          ---------- ---------- ----------
                  10 a,b,c            1234
                  20 d,e,f              23
          • 2. Re: Need output as below
            Frank Kulash
            Hi,

            This is shorter, and I find it simpler:
            SELECT       num
            ,       LISTAGG (ch,   ',') WITHIN GROUP (ORDER BY num2)  
                                       AS mid_val
            ,       REGEXP_SUBSTR ( LISTAGG (num2, ',') WITHIN GROUP (ORDER BY num2)
                             , '\d+'
                           , 1
                           , 2
                           )     AS num2
            FROM      t1
            GROUP BY  num
            ;
            Sorry, I'm not at an Oracle 11.2 database now, so I can't test it.
            NSK2KSN wrote:
            ... and I have used this query
            
            SELECT num, mid_val, num2
            FROM (SELECT t2.num,
            mid_val,
            t2.num2,
            ROW_NUMBER () OVER (PARTITION BY t2.num ORDER BY t2.num) rn
            FROM (  SELECT num,
            LISTAGG (ch, ',') WITHIN GROUP (ORDER BY num)
            AS mid_val
            I think you meant "ORDER BY num<b>2</b> in both the ROW_NUMBER and LISTAGG functions. Since you're PARTITIONing BY (or GROUPing BY) num, every row in the partition or group will have the same value of num, and it's completely arbitrary with row is the 1st, 2nd, 3rd, ...
            • 3. Re: Need output as below
              NSK2KSN
              Hi as per my requirement there will be only three rows and I always want the middle one