Oracle FAQ  Your Portal to the Oracle Knowledge Grid 
Home > Community > Usenet > c.d.o.misc > Re: dividing to group with the same weight
<liorlew_at_gmail.com> a écrit dans le message de news: 1139477809.896487.98670_at_g14g2000cwa.googlegroups.com...
I did the following:
 select name, salary, dept , mod(row_number() over(order by salary
 ),3) group_no from info_table
 order by salary desc;

 the answer I got is not exactly what I need, because I do not need the
 salaries (weight) to be put each time in the following group. I need
 them in the group with the lowest weight.

 I need that if I have the following
 20,20,13,5,5,4,4,4,3,2,1,1,1,1,1,1,1
 the answer for 3 group will be
 20, 20,13
 5
 5
 4, 4, 4
 3, 2,
 1, 1
 1 1, 1
 1
 
 29 , 28, 29 < total
 The query I wrote would return the following instead:
 20 ,20 ,13
 5 ,5 ,4
 4 ,4 ,3
 2 ,1 ,1
 1 ,1 ,1
 1
 
 33, 31, 22 < wrong total

 of course the is always the possibility of opening a cursor and writing
 a C++ or pl/sql program, but I will be more satisfy if I could get
 oracle 10g to do it.

 I am sorry if you got the impression that I want you do do my work for
 me. trust me I am working on it too and will supply an answer if I
 found one.

Yes you can do it in 10g:
SQL> set head on SQL> set head off SQL> col nop noprint SQL> col r format a04 SQL> col g format a100 SQL> def X=3 SQL> col cnt new_value cnt SQL> select count(*) cnt from t; CNT  17
1 row selected.
SQL> with
2 c as ( select count(*) cnt from t ), 3 g as ( select rownum g from dual connect by level <= &X ), 4 s as ( 5 select r, g 6 from ( select rownum1 r from dual, c connect by level <= c.cnt&X+1 ), 7 g 8 ), 9 m as ( 10 select r, g, v 11 from s 12 model 13 return all rows 14 reference val on 15 ( select val, row_number () over (order by val desc) rn 16 from t order by val desc ) 17 dimension by (rn n) 18 measures (val) 19 dimension by (r, g) 20 measures (cast(null as number) v, cast(null as number) s, 21 cast(null as number) m, cast(null as number) f) 22 rules upsert all  sequential order 23 iterate (&cnt) 24 (  calculate sum 25 s[iteration_number,ANY] 26 = nvl(sum(v)[r between 1 and iteration_number1, cv(g)],0), 27  calculate min sum 28 m[iteration_number,1] = min(s)[cv(r),ANY], 29  determine which group to assign > set flag 30 f[iteration_number,ANY] = 31 case when s[cv(r),cv(g)] = m[cv(r),1] 32 then 1  nvl(sum(f)[cv(r),g between 1 and cv(g)1],0) 33 else 0 34 end, 35  assign value 36 v[iteration_number,ANY] = 37 case when f[cv(r),cv(g)] = 1 then val.val[iteration_number] end 38 )
43 row_number () over (order by g) curr, 44 row_number () over (order by g)  1 prev 45 from g )
52 row_number () over (order by g) curr, 53 row_number () over (order by g)  1 prev 54 from g )
61 row_number () over (partition by r order by g) curr, 62 row_number () over (partition by r order by g)  1 prev 63 from m 64 where r > 0 )
72 row_number () over (order by g) curr, 73 row_number () over (order by g)  1 prev 74 from g )
81 row_number () over (order by g) curr, 82 row_number () over (order by g)  1 prev 83 from m 84 where r > 0 85 group by g )
1 20 2 20 3 13 4 5 5 5 6 4 7 4 8 4 9 3 10 2 11 1 12 1 13 1 14 1 15 1 16 1     Tot. 29 29 28
20 rows selected.
Regards
Michel Cadot
Received on Fri Feb 10 2006  10:33:47 CST