Forum Stats

  • 3,751,820 Users
  • 2,250,415 Discussions
  • 7,867,599 Comments

Discussions

Ordering according comparing - SQL Experts pls help

Ayham
Ayham Member Posts: 325
edited Oct 8, 2012 2:22PM in SQL & PL/SQL
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

Best Answer

  • jeneesh
    jeneesh Member Posts: 7,168
    Accepted Answer
    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.
«13

Answers

This discussion has been closed.