Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Sorting and Grouping -Two months in this query

AyhamNov 30 2012 — edited Jan 4 2013
Hi All,

many thanks for jeneesh

i am doing project for construction company, i face this problem in grouping points according to relation between these points the
Relation is from 1 to 100. If the point between this rang that mean there is relation between these points.

this question already solve but the results not correct when the table has more data.

10621782
from jeneesh and many thanks for him.

This example for more clarifications

for example i have these points
id   location         percentage   comments 
1     loc 1,2          20%                that mean point  1 and 2 close to each other by 20%
2     loc 1,3          40%              that mean point 1 and 3 close to each other byy 40%
3     Loc 8,6          25%               that mean point 8 and 6 close to each other by 25% 
4     Loc  6,10        20%
5     LOC 11,10        10 %
6     LOC 15,14         0%
Also , we can see the relation between these points as follwoing
- points 1,2,3 in one group why becuase 1,2 has relation and 1,3 has relation that mean 1,3 also has hidden relation.
- Points 6,8,10,11 in second group there are relations between them .
- but no relation between 1 or 2 or 3 with any point of 6,8,9,10,11
- as well as no relation between 15, 14 that mean 14 in third group and 15 in fourth group.


whati need?

to group the points that has relation according to percentage value ascending


The most important part is to group the points. SO , the below query the gropuing is not correct.

I have the follwoing table with data
drop table temp_value; 
create table temp_value(id number(10),location varchar2(20), percentage number(9)); 
 
 
insert into temp_value values  (1,'LOC 1,2',10); 
insert into  temp_value values (2,'LOC 1,3',0); 
insert into  temp_value values (3,'LOC 1,4',0); 
insert into  temp_value values (4,'LOC 1,5',0); 
insert into  temp_value values (5,'LOC 1,6',0); 
insert into  temp_value values (6,'LOC 2,3',0); 
insert into  temp_value  values(7,'LOC 2,4',0); 
insert into  temp_value values (8,'LOC 2,5',30); 
insert into  temp_value values (9,'LOC 2,6',0); 
insert into  temp_value values (10,'LOC 3,4',0); 
insert into  temp_value values (11,'LOC 3,5',0); 
insert into  temp_value values (12,'LOC 4,5',40); 
insert into  temp_value values (13,'LOC 4,6',0); 
insert into  temp_value values (14,'LOC 6,7',40);
insert into  temp_value values (15,'LOC 7,2',0);
insert into  temp_value values (16,'LOC 8,2',60);
insert into  temp_value values (17,'LOC 8,3',0);
insert into  temp_value values (18,'LOC 3,1',0);
insert into  temp_value values (19,'LOC 9,6',30);
insert into  temp_value values (20,'LOC 11,2',0);
insert into  temp_value values (22,'LOC 12,3',10);
insert into  temp_value values (23,'LOC 19,3',0);
insert into  temp_value values (24,'LOC 17,3',0);
insert into  temp_value values (24,'LOC 20,3',0);
when i used this query , the results is not correct

 with t as
    (select percentage,loc1,loc2,sum(case when percentage = 0 then 1
                       when loc1 in (l1,l2) then 0
                   when loc2 in (l1,l2) then 0
                   when l1 is null and l2 is null then 0
                   else 1
              end) over(order by rn) sm
    from (     select id,location,percentage,
                       regexp_substr(location,'\d+',1,1) LOC1,
                      regexp_substr(location,'\d+',1,2)  LOC2,
                     lag(regexp_substr(location,'\d+',1,1))
                      over(order by percentage desc) l1,
                      lag(regexp_substr(location,'\d+',1,2))
                      over(order by percentage desc) l2,
              row_number() over(order by percentage desc) rn
      from temp_value
      order by percentage desc
        )
  )
   select loc,min(sm)+1 grp
     from(
       select loc,rownum rn,sm
       from(
       select percentage,decode(rn,1,loc1,loc2) loc,sm
       from t a,
            (select 1 rn from dual union all
             select 2 from dual ) b
       order by percentage desc,decode(rn,1,loc1,loc2) asc
      )
   )
    group by loc
   order by min(sm),min(rn);
the results


SQL> /
LOC                         GRP
-------------------- ----------
2                             1
8                             1
6                             2
7                             2
4                             3
5                             3
9                             4
1                             5
12                            6
3                             6
11                           13

LOC                         GRP
-------------------- ----------
19                           14
17                           15
20                           22

14 rows selected.
SQL>


but the correct is
Location        group No
2                  1
8                  1
4                  1
5                  1
1                  1
6                  2
7                  2
9                  2
12                 3
3                  3
19                 4
17                 5
20                 6
many thanks in advance.

Edited by: Ayham on Nov 30, 2012 3:07 AM
This post has been answered by chris227 on Nov 30 2012
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Dec 30 2012
Added on Nov 30 2012
16 comments
288 views