This content has been marked as final.
Show 24 replies
-
15. Re: Ordering according comparing
Web Oct 6, 2012 9:48 AM (in response to Ayham)1 person found this helpful
Edited by: Web on Oct 6, 2012 2:48 PMwith 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
-
16. Re: Ordering according comparing
Ayham Oct 6, 2012 10:03 AM (in response to Web)Many thanks for you.
my regards -
17. Re: Ordering according comparing
Ayham Oct 7, 2012 5:55 AM (in response to Web)Actually when i update the data the group is not correct.Please can you help in that.
SQL> select asd2.loc as Location, asd3.enum as "Group Number" from asd2, asd3 whdrop 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);
ere asd2.groups = asd3.groups;
but the correct should be isLOCATION Group Number ---------- ------------ 4 1 5 1 2 2 1 3 3 4 6 1 6 rows selected.
because the relation between these locationLOCATION Group Number ---------- ------------ 4 1 5 1 2 1 1 1 3 2 6 3
And 3 and 6 no relation with them that mean in different group.[4,5] is 40 [2,5] is 30 [1,2] is 10
regards
Edited by: Ayham on Oct 6, 2012 10:55 PM -
18. Re: Ordering according comparing
Ayham Oct 6, 2012 4:27 PM (in response to Ayham)the groups is not correct.
Many thanks -
19. Re: Ordering according comparing
Venkadesh Raja Oct 8, 2012 8:54 AM (in response to Ayham)Ayham wrote:
Can you Please explain little more your question ?
Actually when i update the date the group is not correct.Please can you help in that.
SQL> select asd2.loc as Location, asd3.enum as "Group Number" from asd2, asd3 whdrop 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);
ere asd2.groups = asd3.groups;
but the correct should be isLOCATION Group Number ---------- ------------ 4 1 5 1 2 2 1 3 3 4 6 1 6 rows selected.
because the relation between these locationLOCATION Group Number ---------- ------------ 4 1 5 1 2 1 1 1 3 2 6 3
And 3 and 6 no relation with them that mean in different group.[4,5] is 40 [2,5] is 30 [1,2] is 10
regards -
20. Re: Ordering according comparing
Ayham Oct 7, 2012 5:46 AM (in response to Venkadesh Raja)Dear Venkadesh ,
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
the nearst location is loc 4,5 becuase the percentage is 40id location percentage 1,'LOC 1,2',10 8,'LOC 2,5',30 12,'LOC 4,5',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
Then i want to show it with group that say these number in one group.5 4 2 1 3 6
the first part of the question for get the ordering points alreay solved by thispoints group 5 1 4 1 2 1 1 1 3 2 6 3
but the second part is not solve correctly which is the group number for points that show the related points.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)
that mean i want to show the points that has relation togather.points group 5 1 4 1 2 1 1 1 3 2 6 3
my regards
Edited by: Ayham on Oct 6, 2012 10:46 PM -
21. Re: Ordering according comparing
Ayham Oct 7, 2012 5:54 AM (in response to Venkadesh Raja)and this is also different data to check
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.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);
but when i used this with the follwoing data the ordering is ok but the grouping is not okwith 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)
SQL> select asd2.loc as Location, asd3.enum as "Group Number" from asd2, asd3 whdrop 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);
ere asd2.groups = asd3.groups;
6 rows selected.LOCATION Group Number ---------- ------------ 4 1 5 1 2 2 1 3 3 4 6 1
but the correct should be is
Edited by: Ayham on Oct 6, 2012 10:54 PMLOCATION Group Number ---------- ------------ 4 1 5 1 2 1 1 1 3 2 6 3
-
22. Re: Ordering according comparing
Ayham Oct 7, 2012 4:26 PM (in response to jeneesh)Still need help? -
23. Re: Ordering according comparing
Ayham Oct 8, 2012 2:58 AM (in response to jeneesh)Hello this is third day for my question ? i Still need help.
Anyboy help me.
many thanks in advance. -
24. Re: Ordering according comparing
Sven W. Oct 8, 2012 6:22 PM (in response to Ayham)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
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.
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]
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