1 2 Previous Next 16 Replies Latest reply: Dec 1, 2012 11:47 PM by Ayham Go to original post RSS
      • 15. Re: Sorting and Grouping - Two months in this query
        Ayham
        But also your Solution not like this that i need it. So the sorting members of groups is not correct.

        the correct sorting is
        LOC     GRP
        2     1
        8     1
        4     1
        5     1
        1     1
        12     2
        3     2
        6     3
        7     3
        9     3
        11     4
        17     5
        19     6
        20     7
        but you show like this
            GRP  LOC
        ---------- ----
                 1    1
                 1    2
                 1    4
                 1    5
                 1    8
                 2    3
                 2   12
                 3    6
                 3    7
                 3    9
                 4   11
                 5   17
                 6   19
                 7   20
        Edited by: Ayham on Dec 1, 2012 9:12 AM
        • 16. Re: Sorting and Grouping - Two months in this query
          Ayham
          when i used the query on 150 rows the SQL Plus hang. 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
          Edited by: Ayham on Dec 1, 2012 9:32 PM

          Edited by: Ayham on Dec 1, 2012 9:46 PM
          1 2 Previous Next