This discussion is archived
1 2 Previous Next 24 Replies Latest reply: Oct 8, 2012 11:22 AM by Sven W. RSS

Ordering according comparing - SQL Experts pls help

Ayham Newbie
Currently Being Moderated
Hi All,

I want to measure the nearest points according to percentage between these points and order it.
by another way, i want to show the relation between points according to percentage value.
 
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',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); 
i want the output like this, the ordering of the point like this
 
4 
3 
5 
6 
1 
2 
or like this
  6 
1 
2 
4 
3 
5 
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] 
regards

Edited by: Ayham on Oct 6, 2012 10:18 AM

Edited by: Ayham on Oct 6, 2012 10:43 AM

Edited by: Ayham on Oct 6, 2012 8:04 PM
  • 1. Re: Ordering according comparing
    Ayham Newbie
    Currently Being Moderated
    Still no reply.

    Is it hard?
  • 2. Re: Ordering according comparing
    Ayham Newbie
    Currently Being Moderated
    Anybody Here can help?
  • 3. Re: Ordering according comparing
    jeneesh Guru
    Currently Being Moderated
    SQL> select loc
      2  from(
      3  select loc,rownum rn
      4  from(
      5  select percentage,location,regexp_substr(location,'\d+',1,rn) loc
      6  from temp_value a,
      7       (select 1 rn from dual union all
      8        select 2 from dual ) b
      9  order by percentage desc,to_number(regexp_substr(location,'\d+',1,rn)) asc
     10  ))
     11  group by loc
     12  order by min(rn);
    
    LOC
    --------------------
    3
    4
    5
    1
    6
    2
    
    6 rows selected.
  • 4. Re: Ordering according comparing
    Ayham Newbie
    Currently Being Moderated
    The solution is ok (perfect) but i want to understand how it's work i mean which query first.

    becuase now in the above solution we have two groups
     
    one is 
    3 
    4 
    5 
    and second is 
    1 
    6 
    2 
    so, i try to show a second column give it name group and i put 1 for first group and group 2 for second group but really i get difficulty in that.

    i want the output will be like this LOC
     
    LOC          Group 
    --------------------   
    3                    1 
    4                    1 
    5                    1 
    1                    2 
    6                    2 
    2                    2 
    regards
  • 5. Re: Ordering according comparing
    Ayham Newbie
    Currently Being Moderated
    I want to put group number
  • 6. Re: Ordering according comparing
    Ayham Newbie
    Currently Being Moderated
    can i do it by SQL or i Have to use PL/SQL.
  • 7. Re: Ordering according comparing
    Ayham Newbie
    Currently Being Moderated
    Any help
  • 8. Re: Ordering according comparing
    Ayham Newbie
    Currently Being Moderated
    still i neeb help
  • 9. Re: Ordering according comparing
    Ayham Newbie
    Currently Being Moderated
    Still need help.

    Edited by: Ayham on Oct 6, 2012 12:13 AM
  • 10. Re: Ordering according comparing
    743274 Newbie
    Currently Being Moderated
    with asd as   
    (
     select loc,rownum as rwm, percentage rn
        from(
        select percentage,location,regexp_substr(location,'\d+',1,rn) loc
        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 as Location, A1.rn as percentage, a2.rw as group_number from asd a1, 
                   (Select rn, row_number() over(order by rn desc) rw
                   from asd
                   group by rn) a2
    where a1.rn = a2.rn
  • 11. Re: Ordering according comparing
    Ayham Newbie
    Currently Being Moderated
    really i am happy to see you reply but the results is not correct

     SQL> select A1.loc as Location, A1.rn as percentage, a2.rw as group_number from
    asd a1,
      2                     (Select rn, row_number() over(order by rn desc) rw
      3                     from asd
      4                     group by rn) a2
      5  where a1.rn = a2.rn;
    
    LOCATION             PERCENTAGE GROUP_NUMBER
    -------------------- ---------- ------------
    4                            90            1
    3                            90            1
    5                            80            2
    3                            80            2
    6                            50            3
    1                            50            3
    2                            30            4
    1                            30            4
    6                            10            5
    2                            10            5
    6                             0            6
    
    LOCATION             PERCENTAGE GROUP_NUMBER
    -------------------- ---------- ------------
    6                             0            6
    5                             0            6
    5                             0            6
    5                             0            6
    5                             0            6
    4                             0            6
    4                             0            6
    4                             0            6
    4                             0            6
    3                             0            6
    3                             0            6
    
    LOCATION             PERCENTAGE GROUP_NUMBER
    -------------------- ---------- ------------
    2                             0            6
    2                             0            6
    2                             0            6
    1                             0            6
    1                             0            6
    1                             0            6
    
    28 rows selected.
    
    SQL>
    it should be like this
    LOC          Group 
    --------------------   
    3                    1 
    4                    1 
    5                    1 
    1                    2 
    6                    2 
    2                    2
    Edited by: Ayham on Oct 6, 2012 1:11 AM

    Edited by: Ayham on Oct 6, 2012 1:11 AM
  • 12. Re: Ordering according comparing
    Ayham Newbie
    Currently Being Moderated
    I am try to do it by PL/SQL with Varray.
    becuase i have try many time to do by SQL but i can not.

    regards
  • 13. 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
       )
    )
    
    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)
    Is it correct ?
  • 14. Re: Ordering according comparing
    Ayham Newbie
    Currently Being Moderated
    the grouping is ok but the number of groups 1,3 not correct . it should be 1,2

    SQL> select A1.loc, (select A2.loc2 from asd A2 where A1.loc = A2.loc and rownum
    = 1) as groups
    2 from asd A1
    3 GROUP BY A1.loc
    4 order by min(A1.rn);
    LOC                  GROUPS
    -------------------- --------------------
    3                    3
    4                    3
    5                    3
    1                    1
    6                    1
    2                    1
    
    6 rows selected.
    
    SQL>
    many thanks i stop writting PL/SQL.


    the output should be like this
     LOC                  GROUPS
    -------------------- --------------------
    3                    2
    4                    2
    5                    2
    1                    1
    6                    1
    2                    1
    or like this
    LOC                  GROUPS
    -------------------- --------------------
    3                    1
    4                    1
    5                    1
    1                    2
    6                    2
    2                    2
    Edited by: Ayham on Oct 6, 2012 2:28 AM

    Edited by: Ayham on Oct 6, 2012 2:29 AM
1 2 Previous Next

Legend

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