1 2 Previous Next 24 Replies Latest reply: Oct 8, 2012 1:22 PM by Sven W. Go to original post RSS
      • 15. Re: Ordering according comparing
        Web
        with asd as (
            select loc, loc2, rownum rn
            from(
            select percentage,location, regexp_substr(location,'\d+',1,rn) loc, regexp_substr(location,'\d+',1,1) loc2
            from temp_value a,
                 (select 1 rn from dual union all
                  select 2 from dual ) b
            order by percentage desc,to_number(regexp_substr(location,'\d+',1,rn)) asc
           )
        ),
         asd2 as (
                       select min(rn) as rwm2, A1.loc, (select A2.loc2 from asd A2 where A1.loc = A2.loc and rownum = 1) as groups
                       from asd A1
                       GROUP BY A1.loc
                       order by min(A1.rn)
                       ),
        asd3 as (
        select groups, row_number() over(order by min(rwm2)) as enum 
        from asd2
        group by groups
        order by min(rwm2)
                            )
        
        
        select asd2.loc as Location, asd3.enum as "Group Number" from asd2, asd3 where asd2.groups = asd3.groups
        Edited by: Web on Oct 6, 2012 2:48 PM
        • 16. Re: Ordering according comparing
          Ayham
          Many thanks for you.

          my regards
          • 17. Re: Ordering according comparing
            Ayham
            Actually when i update the data the group is not correct.Please can you help in that.
            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 5,6',0); 
            SQL> select asd2.loc as Location, asd3.enum as "Group Number" from asd2, asd3 wh
            ere asd2.groups = asd3.groups;
            LOCATION   Group Number 
            ---------- ------------ 
            4                     1 
            5                     1 
            2                     2 
            1                     3 
            3                     4 
            6                     1 
            
            6 rows selected. 
            but the correct should be is
            LOCATION   Group Number 
            ---------- ------------ 
            4                     1 
            5                     1 
            2                     1 
            1                     1 
            3                     2 
            6                     3 
            because the relation between these location
            [4,5] is 40 
            [2,5] is 30 
            [1,2] is 10 
            And 3 and 6 no relation with them that mean in different group.

            regards

            Edited by: Ayham on Oct 6, 2012 10:55 PM
            • 18. Re: Ordering according comparing
              Ayham
              the groups is not correct.
              Many thanks
              • 19. Re: Ordering according comparing
                Venkadesh Raja
                Ayham wrote:
                Actually when i update the date the group is not correct.Please can you help in that.
                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 5,6',0); 
                SQL> select asd2.loc as Location, asd3.enum as "Group Number" from asd2, asd3 wh
                ere asd2.groups = asd3.groups;
                LOCATION   Group Number 
                ---------- ------------ 
                4                     1 
                5                     1 
                2                     2 
                1                     3 
                3                     4 
                6                     1 
                
                6 rows selected. 
                but the correct should be is
                LOCATION   Group Number 
                ---------- ------------ 
                4                     1 
                5                     1 
                2                     1 
                1                     1 
                3                     2 
                6                     3 
                because the relation between these location
                [4,5] is 40 
                [2,5] is 30 
                [1,2] is 10 
                And 3 and 6 no relation with them that mean in different group.

                regards
                Can you Please explain little more your question ?
                • 20. Re: Ordering according comparing
                  Ayham
                  Dear Venkadesh ,
                  i have table inside this table three columns one for id , location and third for percentages.

                  i want to get the relation between these locations.
                  we can see the relation from percentage column that are
                   id  location percentage 
                   1,'LOC 1,2',10
                   8,'LOC 2,5',30
                   12,'LOC 4,5',40
                  the nearst location is loc 4,5 becuase the percentage is 40
                  then loc 2,5 becuase percentage is 30
                  then 1,2 becuase the percentage is 10

                  also we can see that there are relation between 1,5 through 2

                  so , in this example 5,4 ,2,1 is one group becasue it has relation between points 4,5 one point related with 2,5 and 2,5 related with 1,2 .

                  so the results should be
                  5
                  4
                  2
                  1
                  3
                  6
                  Then i want to show it with group that say these number in one group.
                  points         group 
                  5                 1
                  4                 1
                  2                 1           
                  1                  1
                  3                  2
                  6                  3
                  the first part of the question for get the ordering points alreay solved by this
                  with asd as (
                      select loc, loc2, rownum rn
                      from(
                      select percentage,location, regexp_substr(location,'\d+',1,rn) loc, regexp_substr(location,'\d+',1,1) loc2
                      from temp_value a,
                           (select 1 rn from dual union all
                            select 2 from dual ) b
                      order by percentage desc,to_number(regexp_substr(location,'\d+',1,rn)) asc
                     )
                  )
                   
                  select A1.loc, (select A2.loc2 from asd A2 where A1.loc = A2.loc and rownum = 1) as groups
                  from asd A1
                  GROUP BY A1.loc
                  order by min(A1.rn)
                  but the second part is not solve correctly which is the group number for points that show the related points.
                  points         group 
                  5                  1
                  4                  1
                  2                  1           
                  1                  1
                  3                  2
                  6                  3
                  that mean i want to show the points that has relation togather.


                  my regards

                  Edited by: Ayham on Oct 6, 2012 10:46 PM
                  • 21. Re: Ordering according comparing
                    Ayham
                    and this is also different data to check
                    insert into temp_value values  (1,'LOC 1,2',30); 
                    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',50); 
                    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 (9,'LOC 2,5',0); 
                    insert into  temp_value values (10,'LOC 2,6',10); 
                    insert into  temp_value values (11,'LOC 3,4',90); 
                    insert into  temp_value values (12,'LOC 3,5',80); 
                    insert into  temp_value values (13,'LOC 4,5',0); 
                    insert into  temp_value values (14,'LOC 4,6',0); 
                    insert into  temp_value values (15,'LOC 5,6',0);
                    when i used the follwoing query to show ordering and grouping , it's working ok but when i update the data the results not correct , i don't know where is the mistake.
                    with asd as (
                        select loc, loc2, rownum rn
                        from(
                        select percentage,location, regexp_substr(location,'\d+',1,rn) loc, regexp_substr(location,'\d+',1,1) loc2
                        from temp_value a,
                             (select 1 rn from dual union all
                              select 2 from dual ) b
                        order by percentage desc,to_number(regexp_substr(location,'\d+',1,rn)) asc
                       )
                    )
                     
                    select A1.loc, (select A2.loc2 from asd A2 where A1.loc = A2.loc and rownum = 1) as groups
                    from asd A1
                    GROUP BY A1.loc
                    order by min(A1.rn)
                    but when i used this with the follwoing data the ordering is ok but the grouping is not ok
                    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 5,6',0);
                    SQL> select asd2.loc as Location, asd3.enum as "Group Number" from asd2, asd3 wh
                    ere asd2.groups = asd3.groups;
                    LOCATION   Group Number 
                    ---------- ------------ 
                    4                     1 
                    5                     1 
                    2                     2 
                    1                     3 
                    3                     4 
                    6                     1 
                     
                    6 rows selected.



                    but the correct should be is
                    LOCATION   Group Number 
                    ---------- ------------ 
                    4                     1 
                    5                     1 
                    2                     1 
                    1                     1 
                    3                     2 
                    6                     3
                    Edited by: Ayham on Oct 6, 2012 10:54 PM
                    • 22. Re: Ordering according comparing
                      Ayham
                      Still need help?
                      • 23. Re: Ordering according comparing
                        Ayham
                        Hello this is third day for my question ? i Still need help.
                        Anyboy help me.

                        many thanks in advance.
                        • 24. Re: Ordering according comparing
                          Sven W.
                          THe problem why you get only little help is because you didn't describe your case properly.
                          What is missing is a good explaination why something didn't work. You always only say: This is missing. Or this doesn't work.

                          You need to give the output and a good explaination about the output. Like a mathematical formula that you want to apply.

                          For example:
                          if you ask me why this ordering i will say that

                          4 [90 percent between point 3,4 that mean can be 3,4 or 4,3]
                          3
                          5 [80 percent between 3,5 that mean can be 5 after 3,4]
                          6 [50 percent between 1,6 that mean can be 1,6 or 6,1 ]
                          1
                          2 [ 30 percent between 1,2 that mean can be 1,2 or 2,1]
                          I have no idea what you mean. This all sounds like meaningless technical numbers to me. You can hardcode this numbers. But I guess there is a certain logic behind it and hardcoding would not be a good way.

                          Furthermore constantly pushing the tread let to the effect that the answers are now distributed over several pages and it can't be seen anymore what others already answered. And it is against the forum etiquette, btw.

                          Edited by: Sven W. on Oct 8, 2012 8:19 PM
                          1 2 Previous Next