## Forum Stats

• 3,751,820 Users
• 2,250,415 Discussions

Discussions

# Ordering according comparing - SQL Experts pls help

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

«13

Is it hard?
• Anybody Here can help?
• ```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.```
• 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
• I want to put group number
• can i do it by SQL or i Have to use PL/SQL.
• Any help
• still i neeb help
• Still need help.

Edited by: Ayham on Oct 6, 2012 12:13 AM
• ```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```
This discussion has been closed.