this query solved by chris227 and others . Many thanks for them.
2472588
So, when i execute it on 20 rows work fine but when i execute it on 150 rows. the sql plus hanging no results
drop table temp_value;
create table temp_value(id number(10),loc1 varchar2(20),loc2 varchar2(20), percentage number(9));
insert into temp_value values (1 , 0001 , 0010 ,30);
insert into temp_value values (2 , 0001 , 0011 ,30);
insert into temp_value values (3 , 0001 , 0012 ,20);
insert into temp_value values (4 , 0001 , 0013 ,30);
insert into temp_value values (5 , 0001 , 0014 ,40);
insert into temp_value values (6 , 0001 , 0017 ,50);
insert into temp_value values (7 , 0001 , 0018 ,40);
insert into temp_value values (8 , 0001 , 0002 ,80);
insert into temp_value values (9 , 0001 , 0020 ,90);
insert into temp_value values (10 , 0001 , 0004 ,0);
insert into temp_value values (11 , 0001 , 0005 ,0);
insert into temp_value values (12 , 0001 , 0006 ,0);
insert into temp_value values (13 , 0001 , 0009 ,20);
insert into temp_value values (14 , 0010 , 0011 ,30);
insert into temp_value values (15 , 0010 , 0012 ,40);
insert into temp_value values (16 , 0010 , 0013 ,50);
insert into temp_value values (17 , 0010 , 0014 ,20);
insert into temp_value values (18 , 0010 , 0015 ,0);
insert into temp_value values (19 , 0010 , 0016 ,0);
insert into temp_value values (20 , 0010 , 0017 ,0);
insert into temp_value values (21 , 0010 , 0018 ,40);
insert into temp_value values (22 , 0010 , 0002 ,20);
insert into temp_value values (23 , 0010 , 0020 ,10);
insert into temp_value values (24 , 0010 , 0021 ,40);
insert into temp_value values (25 , 0010 , 0004 ,50);
insert into temp_value values (26 , 0010 , 0005 ,70);
insert into temp_value values (27 , 0010 , 0006 ,80);
insert into temp_value values (28 , 0010 , 0008 ,90);
insert into temp_value values (29 , 0011 , 0012 ,30);
insert into temp_value values (30 , 0011 , 0013 ,40);
insert into temp_value values (31 , 0011 , 0014 ,20);
insert into temp_value values (32 , 0011 , 0015 ,40);
insert into temp_value values (33 , 0011 , 0016 ,20);
insert into temp_value values (34 , 0011 , 0017 ,40);
insert into temp_value values (35 , 0011 , 0018 ,60);
insert into temp_value values (36 , 0011 , 0019 ,70);
insert into temp_value values (37 , 0011 , 0002 ,60);
insert into temp_value values (38 , 0011 , 0020 ,0);
insert into temp_value values (39 , 0011 , 0021 ,0);
insert into temp_value values (40 , 0011 , 0003 ,0);
insert into temp_value values (41 , 0011 , 0004 ,0);
insert into temp_value values (42 , 0011 , 0005 ,0);
insert into temp_value values (43 , 0011 , 0006 ,30);
insert into temp_value values (44 , 0011 , 0008 ,20);
insert into temp_value values (45 , 0011 , 0009 ,40);
insert into temp_value values (46 , 0012 , 0013 ,20);
insert into temp_value values (47 , 0012 , 0014 ,0);
insert into temp_value values (48 , 0012 , 0015 ,0);
insert into temp_value values (49 , 0012 , 0016 ,0);
insert into temp_value values (50 , 0012 , 0017 ,30);
insert into temp_value values (51 , 0012 , 0018 ,40);
insert into temp_value values (52 , 0012 , 0002 ,20);
insert into temp_value values (53 , 0012 , 0020 ,0);
insert into temp_value values (54 , 0012 , 0021 ,0);
insert into temp_value values (55 , 0012 , 0004 ,0);
insert into temp_value values (56 , 0012 , 0005 ,0);
insert into temp_value values (57 , 0012 , 0006 ,30);
insert into temp_value values (58 , 0012 , 0007 ,20);
insert into temp_value values (59 , 0012 , 0008 ,0);
insert into temp_value values (60 , 0013 , 0014 ,0);
insert into temp_value values (61 , 0013 , 0015 ,0);
insert into temp_value values (62 , 0013 , 0016 ,3);
insert into temp_value values (63 , 0013 , 0017 ,0);
insert into temp_value values (64 , 0013 , 0018 ,0);
insert into temp_value values (65 , 0013 , 0019 ,0);
insert into temp_value values (66 , 0013 , 0002 ,20);
insert into temp_value values (67 , 0013 , 0020 ,20);
insert into temp_value values (68 , 0013 , 0003 ,0);
insert into temp_value values (69 , 0013 , 0004 ,30);
insert into temp_value values (70 , 0013 , 0005 ,40);
insert into temp_value values (71 , 0013 , 0006 ,50);
insert into temp_value values (72 , 0013 , 0008 ,0);
insert into temp_value values (73 , 0014 , 0016 ,0);
insert into temp_value values (74 , 0014 , 0017 ,0);
insert into temp_value values (75 , 0014 , 0018 ,0);
insert into temp_value values (76 , 0014 , 0021 ,0);
insert into temp_value values (77 , 0014 , 0004 ,0);
insert into temp_value values (78 , 0014 , 0005 ,0);
insert into temp_value values (79 , 0014 , 0006 ,0);
insert into temp_value values (80 , 0014 , 0008 ,0);
insert into temp_value values (81 , 0015 , 0016 ,0);
insert into temp_value values (82 , 0015 , 0017 ,0);
insert into temp_value values (83 , 0015 , 0018 ,0);
insert into temp_value values (84 , 0015 , 0019 ,20);
insert into temp_value values (85 , 0015 , 0002 ,20);
insert into temp_value values (86 , 0015 , 0020 ,60);
insert into temp_value values (87 , 0015 , 0021 ,70);
insert into temp_value values (88 , 0015 , 0003 ,80);
insert into temp_value values (89 , 0015 , 0004 ,60);
insert into temp_value values (90 , 0015 , 0006 ,0);
insert into temp_value values (91 , 0015 , 0007 ,0);
insert into temp_value values (92 , 0015 , 0008 ,0);
insert into temp_value values (93 , 0015 , 0009 ,0);
insert into temp_value values (94 , 0016 , 0017 ,0);
insert into temp_value values (95 , 0016 , 0018 ,0);
insert into temp_value values (96 , 0016 , 0002 ,0);
insert into temp_value values (97 , 0016 , 0020 ,0);
insert into temp_value values (98 , 0016 , 0021 ,70);
insert into temp_value values (99 , 0016 , 0003 ,60);
insert into temp_value values (100 , 0016 , 0004 ,50);
insert into temp_value values (101 , 0016 , 0006 ,40);
insert into temp_value values (102 , 0016 , 0008 ,60);
insert into temp_value values (103 , 0017 , 0018 ,70);
insert into temp_value values (104 , 0017 , 0002 ,40);
insert into temp_value values (105 , 0017 , 0020 ,0);
insert into temp_value values (106 , 0017 , 0021 ,0);
insert into temp_value values (107 , 0017 , 0003 ,0);
insert into temp_value values (108 , 0017 , 0004 ,0);
insert into temp_value values (109 , 0017 , 0005 ,0);
insert into temp_value values (110 , 0017 , 0006 ,0);
insert into temp_value values (111 , 0017 , 0008 ,0);
insert into temp_value values (112 , 0018 , 0019 ,0);
insert into temp_value values (113 , 0018 , 0002 ,60);
insert into temp_value values (114 , 0018 , 0020 ,70);
insert into temp_value values (115 , 0018 , 0021 ,80);
insert into temp_value values (116 , 0018 , 0004 ,20);
insert into temp_value values (117 , 0018 , 0005 ,10);
insert into temp_value values (118 , 0018 , 0006 ,0);
insert into temp_value values (119 , 0018 , 0008 ,0);
insert into temp_value values (120 , 0019 , 0020 ,0);
insert into temp_value values (121 , 0002 , 0020 ,0);
insert into temp_value values (122 , 0002 , 0021 ,0);
insert into temp_value values (123 , 0002 , 0003 ,0);
insert into temp_value values (124 , 0002 , 0004 ,0);
insert into temp_value values (125 , 0002 , 0005 ,0);
insert into temp_value values (126 , 0002 , 0006 ,0);
insert into temp_value values (127 , 0002 , 0007 ,20);
insert into temp_value values (128 , 0002 , 0008 ,20);
insert into temp_value values (129 , 0002 , 0009 ,10);
insert into temp_value values (130 , 0020 , 0021 ,0);
insert into temp_value values (131 , 0020 , 0004 ,0);
insert into temp_value values (132 , 0020 , 0005 ,0);
insert into temp_value values (133 , 0020 , 0006 ,0);
insert into temp_value values (134 , 0020 , 0008 ,0);
insert into temp_value values (135 , 0021 , 0003 ,0);
insert into temp_value values (136 , 0021 , 0004 ,0);
insert into temp_value values (137 , 0021 , 0006 ,0);
insert into temp_value values (138 , 0021 , 0008 ,10);
insert into temp_value values (139 , 0021 , 0009 ,10);
insert into temp_value values (140 , 0003 , 0006 ,20);
insert into temp_value values (141 , 0003 , 0008 ,10);
insert into temp_value values (142 , 0003 , 0009 ,20);
insert into temp_value values (143 , 0004 , 0005 ,0);
insert into temp_value values (144 , 0004 , 0006 ,0);
insert into temp_value values (145 , 0004 , 0008 ,0);
insert into temp_value values (146 , 0005 , 0006 ,0);
insert into temp_value values (147 , 0005 , 0009 ,0);
insert into temp_value values (148 , 0006 , 0007 ,0);
insert into temp_value values (149 , 0006 , 0008 ,0);
insert into temp_value values (150 , 0006 , 0009 ,0);
using this
with connects as (
select distinct
loc1
,loc2
,connect_by_root(loc1) grp
,percentage per
from
temp_value
--start with
--percentage != 0
connect by nocycle
(prior loc2 = loc1
or
prior loc1 = loc2
or
prior loc1 = loc1
or
prior loc2 = loc2)
and
percentage != 0
and
prior percentage != 0
)
select
loc
,dense_rank() over (order by decode(per,0,1,0), grp) grp
from (
select
loc
,max(grp) keep (dense_rank first order by per desc, grp) grp
,max(per) keep (dense_rank last order by per nulls first) per
from (
select
loc1 loc
,grp
,per
from connects
union
select
loc2
,grp
,per
from connects
)
group by
loc )
order by 2,per desc,1
regards
Ayham
Edited by: Ayham on Dec 1, 2012 9:47 PM
Edited by: Ayham on 03-Dec-2012 01:50
Edited by: Ayham on 03-Dec-2012 01:50