1 2 Previous Next 24 Replies Latest reply: Oct 8, 2012 1:22 PM by Sven W. RSS

    Ordering according comparing - SQL Experts pls help

    Ayham
      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
          Still no reply.

          Is it hard?
          • 2. Re: Ordering according comparing
            Ayham
            Anybody Here can help?
            • 3. Re: Ordering according comparing
              jeneesh
              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
                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
                  I want to put group number
                  • 6. Re: Ordering according comparing
                    Ayham
                    can i do it by SQL or i Have to use PL/SQL.
                    • 7. Re: Ordering according comparing
                      Ayham
                      Any help
                      • 8. Re: Ordering according comparing
                        Ayham
                        still i neeb help
                        • 9. Re: Ordering according comparing
                          Ayham
                          Still need help.

                          Edited by: Ayham on Oct 6, 2012 12:13 AM
                          • 10. Re: Ordering according comparing
                            Web
                            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
                              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
                                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
                                  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
                                     )
                                  )
                                  
                                  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
                                    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