This discussion is archived
1 2 Previous Next 24 Replies Latest reply: Oct 8, 2012 11:22 AM by Sven W. Go to original post
• ###### 15. 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
)
),
asd2 as (
select min(rn) as rwm2, 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)
),
asd3 as (
select groups, row_number() over(order by min(rwm2)) as enum
from asd2
group by groups
order by min(rwm2)
)

select asd2.loc as Location, asd3.enum as "Group Number" from asd2, asd3 where asd2.groups = asd3.groups``````
Edited by: Web on Oct 6, 2012 2:48 PM
• ###### 16. Re: Ordering according comparing
Currently Being Moderated
Many thanks for you.

my regards
• ###### 17. Re: Ordering according comparing
Currently Being Moderated
Actually when i update the data the group is not correct.Please can you help in that.
``````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',10);
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',0);
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 (8,'LOC 2,5',30);
insert into  temp_value values (9,'LOC 2,6',0);
insert into  temp_value values (10,'LOC 3,4',0);
insert into  temp_value values (11,'LOC 3,5',0);
insert into  temp_value values (12,'LOC 4,5',40);
insert into  temp_value values (13,'LOC 4,6',0);
insert into  temp_value values (14,'LOC 5,6',0); ``````
SQL> select asd2.loc as Location, asd3.enum as "Group Number" from asd2, asd3 wh
ere asd2.groups = asd3.groups;
``````LOCATION   Group Number
---------- ------------
4                     1
5                     1
2                     2
1                     3
3                     4
6                     1

6 rows selected. ``````
but the correct should be is
``````LOCATION   Group Number
---------- ------------
4                     1
5                     1
2                     1
1                     1
3                     2
6                     3 ``````
because the relation between these location
``````[4,5] is 40
[2,5] is 30
[1,2] is 10 ``````
And 3 and 6 no relation with them that mean in different group.

regards

Edited by: Ayham on Oct 6, 2012 10:55 PM
• ###### 18. Re: Ordering according comparing
Currently Being Moderated
the groups is not correct.
Many thanks
• ###### 19. Re: Ordering according comparing
Currently Being Moderated
Ayham wrote:
Actually when i update the date the group is not correct.Please can you help in that.
``````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',10);
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',0);
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 (8,'LOC 2,5',30);
insert into  temp_value values (9,'LOC 2,6',0);
insert into  temp_value values (10,'LOC 3,4',0);
insert into  temp_value values (11,'LOC 3,5',0);
insert into  temp_value values (12,'LOC 4,5',40);
insert into  temp_value values (13,'LOC 4,6',0);
insert into  temp_value values (14,'LOC 5,6',0); ``````
SQL> select asd2.loc as Location, asd3.enum as "Group Number" from asd2, asd3 wh
ere asd2.groups = asd3.groups;
``````LOCATION   Group Number
---------- ------------
4                     1
5                     1
2                     2
1                     3
3                     4
6                     1

6 rows selected. ``````
but the correct should be is
``````LOCATION   Group Number
---------- ------------
4                     1
5                     1
2                     1
1                     1
3                     2
6                     3 ``````
because the relation between these location
``````[4,5] is 40
[2,5] is 30
[1,2] is 10 ``````
And 3 and 6 no relation with them that mean in different group.

regards
• ###### 20. Re: Ordering according comparing
Currently Being Moderated
i have table inside this table three columns one for id , location and third for percentages.

i want to get the relation between these locations.
we can see the relation from percentage column that are
`````` id  location percentage
1,'LOC 1,2',10
8,'LOC 2,5',30
12,'LOC 4,5',40``````
the nearst location is loc 4,5 becuase the percentage is 40
then loc 2,5 becuase percentage is 30
then 1,2 becuase the percentage is 10

also we can see that there are relation between 1,5 through 2

so , in this example 5,4 ,2,1 is one group becasue it has relation between points 4,5 one point related with 2,5 and 2,5 related with 1,2 .

so the results should be
``````5
4
2
1
3
6``````
Then i want to show it with group that say these number in one group.
``````points         group
5                 1
4                 1
2                 1
1                  1
3                  2
6                  3``````
the first part of the question for get the ordering points alreay solved by this
``````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)``````
but the second part is not solve correctly which is the group number for points that show the related points.
``````points         group
5                  1
4                  1
2                  1
1                  1
3                  2
6                  3``````
that mean i want to show the points that has relation togather.

my regards

Edited by: Ayham on Oct 6, 2012 10:46 PM
• ###### 21. Re: Ordering according comparing
Currently Being Moderated
and this is also different data to check
``````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);``````
when i used the follwoing query to show ordering and grouping , it's working ok but when i update the data the results not correct , i don't know where is the mistake.
``````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)``````
but when i used this with the follwoing data the ordering is ok but the grouping is not ok
``````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',10);
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',0);
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 (8,'LOC 2,5',30);
insert into  temp_value values (9,'LOC 2,6',0);
insert into  temp_value values (10,'LOC 3,4',0);
insert into  temp_value values (11,'LOC 3,5',0);
insert into  temp_value values (12,'LOC 4,5',40);
insert into  temp_value values (13,'LOC 4,6',0);
insert into  temp_value values (14,'LOC 5,6',0);``````
SQL> select asd2.loc as Location, asd3.enum as "Group Number" from asd2, asd3 wh
ere asd2.groups = asd3.groups;
``````LOCATION   Group Number
---------- ------------
4                     1
5                     1
2                     2
1                     3
3                     4
6                     1
``````
6 rows selected.

but the correct should be is
``````LOCATION   Group Number
---------- ------------
4                     1
5                     1
2                     1
1                     1
3                     2
6                     3``````
Edited by: Ayham on Oct 6, 2012 10:54 PM
• ###### 22. Re: Ordering according comparing
Currently Being Moderated
Still need help?
• ###### 23. Re: Ordering according comparing
Currently Being Moderated
Hello this is third day for my question ? i Still need help.
Anyboy help me.

• ###### 24. Re: Ordering according comparing
Currently Being Moderated
THe problem why you get only little help is because you didn't describe your case properly.
What is missing is a good explaination why something didn't work. You always only say: This is missing. Or this doesn't work.

You need to give the output and a good explaination about the output. Like a mathematical formula that you want to apply.

For example:
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]
I have no idea what you mean. This all sounds like meaningless technical numbers to me. You can hardcode this numbers. But I guess there is a certain logic behind it and hardcoding would not be a good way.

Furthermore constantly pushing the tread let to the effect that the answers are now distributed over several pages and it can't be seen anymore what others already answered. And it is against the forum etiquette, btw.

Edited by: Sven W. on Oct 8, 2012 8:19 PM
1 2 Previous Next

#### Legend

• Correct Answers - 10 points