Ordering according comparing - SQL Experts pls ...

15. Re: Ordering according comparing

Web Oct 6, 2012 9:48 AM (in response to Ayham)

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

1 person found this helpful

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.

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

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:
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

Can you Please explain little more your question ?

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

 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

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

Ayham Oct 7, 2012 5:54 AM (in response to Venkadesh Raja)

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

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

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