This content has been marked as final. Show 3 replies
Well, depends what you mean by "middle"... What if there are more than 3 rows per group?1 person found this helpful
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
Hi,1 person found this helpful
This is shorter, and I find it simpler:
Sorry, I'm not at an Oracle 11.2 database now, so I can't test it.
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 ;
NSK2KSN wrote: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, ...
... 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
Hi as per my requirement there will be only three rows and I always want the middle one