This discussion is archived
1 2 Previous Next 24 Replies Latest reply: Oct 8, 2012 11:22 AM by Sven W. Go to original post RSS
  • 15. Re: Ordering according comparing
    743274 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Many thanks for you.

    my regards
  • 17. Re: Ordering according comparing
    Ayham Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    the groups is not correct.
    Many thanks
  • 19. Re: Ordering according comparing
    Venkadesh Raja Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Still need help?
  • 23. Re: Ordering according comparing
    Ayham Newbie
    Currently Being Moderated
    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. Guru
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points