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

# Ordering according comparing - SQL Experts pls help

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
Currently Being Moderated

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

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