Home » SQL & PL/SQL » SQL & PL/SQL » How to find the common values among groups of values
How to find the common values among groups of values [message #190137] Tue, 29 August 2006 06:37 Go to next message
gold_oracl
Messages: 129
Registered: July 2006
Location: Westborough, MA
Senior Member
Hi all,
my table have two columns called id, name

ID NAME
------------
10 AA BB CC
10 AA BB
10 AA BB DD
20 CC DD FF
20 CC DD FF GG HH II JJ
20 CC DD FF II

The output i want
ID NAME
------------
10 AA BB
20 CC DD FF

Thanks,
Thangam
Re: How to find the common values among groups of values [message #190138 is a reply to message #190137] Tue, 29 August 2006 06:55 Go to previous messageGo to next message
sunsanvin
Messages: 60
Registered: April 2006
Location: Hyderabad
Member

First create a view on first four columns

then use select statement with where cndition with null

where column3=null

like that
try in this fashion you will get it
Re: How to find the common values among groups of values [message #190139 is a reply to message #190138] Tue, 29 August 2006 06:57 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
sunsanvin wrote on Tue, 29 August 2006 13:55

where column3=null

like that
try in this fashion you will get it
No, not like that. NULLs can't be compared that way. Back to the drawing board Wink

MHE
Re: How to find the common values among groups of values [message #190141 is a reply to message #190138] Tue, 29 August 2006 07:00 Go to previous messageGo to next message
gold_oracl
Messages: 129
Registered: July 2006
Location: Westborough, MA
Senior Member
Well here i have only two columns named ID, NAME not four columns.
where 10 is ID and "AA BB CC" is NAME


ID NAME
-- ----------
10 AA BB CC
10 AA BB
10 AA BB DD
20 CC DD FF
20 CC DD FF GG HH II JJ
20 CC DD FF II

The output i want
ID NAME
-- ----------
10 AA BB
20 CC DD FF

hope this is clear now

Thanks,
Thangam
Re: How to find the common values among groups of values [message #190143 is a reply to message #190137] Tue, 29 August 2006 07:23 Go to previous messageGo to next message
gopi_ora
Messages: 13
Registered: August 2006
Location: Bangalore
Junior Member
Hi,
Not sure what is your expectation ...

If the expected output is
ID NAME
-- ----------
10 AA BB
20 CC DD FF

Then
try this
Assuming t2 as table

 select * from t2 where length(name) between decode(id,10,5,20,5) and decode(id,10,5,20,8) 




Thanks.

[Updated on: Tue, 29 August 2006 07:32]

Report message to a moderator

Re: How to find the common values among groups of values [message #190147 is a reply to message #190143] Tue, 29 August 2006 07:34 Go to previous messageGo to next message
gold_oracl
Messages: 129
Registered: July 2006
Location: Westborough, MA
Senior Member
Gobi,
Thanks for your reply. but however, your query will not work in all the cases. because in my table i have many id's like 10, 20, 30, 40 etc.

let me clarify my question.

My table is having two columns

ID     NAME
--     ----------
10     This is test
10     This is test for Oracle
10     This is test event
20     CC DD FF
20     CC DD FF GG HH II JJ
20     CC DD FF II
30     DD FF GG DD HH II
30     DD FF GG DD HH II MM

The output i want
ID     NAME
--     ----------
10     This is test 
20     CC DD FF
30     DD FF GG DD HH II


where name is the field which contains some statement. i want to findout common words exist among the groups.

is it clear now??

Thanks,
Thangam


Re: How to find the common values among groups of values [message #190148 is a reply to message #190137] Tue, 29 August 2006 07:37 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Looking at this logically, you are looking for the shortest string in column NAME, as this will be the one that is in all the others.

create table temp_instr (id number, col_1  varchar2(30));

insert into temp_instr values (10 ,'AA BB CC');
insert into temp_instr values (10 ,'AA BB');
insert into temp_instr values (10 ,'AA BB DD');
insert into temp_instr values (20 ,'CC DD FF');
insert into temp_instr values (20 ,'CC DD FF GG HH II JJ');
insert into temp_instr values (20 ,'CC DD FF II');

commit;

select id, min(col_1) keep (dense_rank first order by length(col_1))
from   temp_instr
group by id;
Re: How to find the common values among groups of values [message #190153 is a reply to message #190148] Tue, 29 August 2006 07:49 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Or, if you want the string that appears in the largest number of other strings for the same ID, try this beast:

select id, col_1 
from ( select id
             ,col_1
             ,matches
             ,max(matches) over (partition by id) max_matches
       from (select t1.id
                   ,t1.col_1
                   ,sum(decode(instr(t2.col_1, t1.col_1),0,0,1)) matches
             from   temp_instr t1
                   ,temp_instr t2
             where  t1.id = t2.id
             group by t1.id,t1.col_1))
where matches = max_matches;
Re: How to find the common values among groups of values [message #190154 is a reply to message #190147] Tue, 29 August 2006 07:51 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Normally you would do this using INTERSECT, but you seem to have a flaw in your design.
You store more than 1 value in a single column and expect some logic to be processed based on the different values.
Re: How to find the common values among groups of values [message #190158 is a reply to message #190154] Tue, 29 August 2006 08:29 Go to previous messageGo to next message
gold_oracl
Messages: 129
Registered: July 2006
Location: Westborough, MA
Senior Member
Thanks JRowbottom for your prompt reply.
Re: How to find the common values among groups of values [message #190170 is a reply to message #190158] Tue, 29 August 2006 09:31 Go to previous messageGo to next message
gold_oracl
Messages: 129
Registered: July 2006
Location: Westborough, MA
Senior Member
JRowbottom,
this is not fulfilling in all the cases.
for example if the records like this...
ID   NAME
36   Blue Advantage Option II 
36   Blue Advantage Option III 
36   Blue Advantage Option IV
36   Blue Advantage Option XII 
48   Seniors First Basic Care 
48   Seniors First Special Care                                 
27   JRowbottom
27   Alex

in this case my output should be

ID   NAME
36   Blue Advantage Option
48   Seniors First 

Further, if there is no common words across the id(for 27), then it should not come in the ouput.



Thanks,
Thangam
Re: How to find the common values among groups of values [message #190172 is a reply to message #190170] Tue, 29 August 2006 09:43 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It wouldn't do, would it?

All your examples were of the form 'Which of these strings appears in all of the others'.
My code matches all of the examples you have posted so far.
In none of the examples did you post anything like this :
ID    NAME
11   AA BB CC
11   AA BB EE
11   AA BB CC FF

Expected Result
11   AA BB
which is what you seem to be asking for now.
In order to clarify your requirement:
1) Is the example I have just posted correct
2) If the row
11    AA XX BB
was added to the data, would the correct answer still be 'AA BB'?
Re: How to find the common values among groups of values [message #190180 is a reply to message #190172] Tue, 29 August 2006 10:12 Go to previous messageGo to next message
gold_oracl
Messages: 129
Registered: July 2006
Location: Westborough, MA
Senior Member
i apologies for incomplete information.

check the data and result for your query.


SQL> select * from tab2 order by 1;

        ID NAME
---------- ----------------------------------------------------------------
        10 This is test
        10 This is test for Oracle
        10 This is test event
        10 that is test
        20 CC DD FF
        20 CC DD FF GG HH II JJ
        20 CC DD FF II
        27 JRowbottom
        27 Alex
        30 DD FF GG DD HH II
        30 DD FF GG DD HH II MM
        36 Blue Advantage Option II
        36 Blue Advantage Option I
        36 Blue Advantage Option III
        40 DD FF GG DD HH II MM
        50 HH II MM
        70 AA BB CC
        70 AA BB
        70 AA BB DD

19 rows selected.

SQL> 
SQL> select id, min(name) keep (dense_rank first order by length(name))
  2  from   tab2
group by id;  3  

        ID MIN(NAME)KEEP(DENSE_RANKFIRSTORDERBYLENGTH(NAME))
---------- ---------------------------------------------------------------------
        10 This is test
        20 CC DD FF
        27 Alex
        30 DD FF GG DD HH II
        36 Blue Advantage Option I
        40 DD FF GG DD HH II MM
        50 HH II MM
        70 AA BB

8 rows selected.

SQL> select id, name 
  2  from ( select id
             ,name
             ,matches
             ,max(matches) over (partition by id) max_matches
       from (select t1.id
                   ,t1.name
                   ,sum(decode(instr(t2.name, t1.name),0,0,1)) matches
             from   tab2 t1
                   ,tab2 t2
             where  t1.id = t2.id
             group by t1.id,t1.name))
where matches = max_matches;  3    4    5    6    7    8    9   10   11   12   13  

        ID NAME
---------- ---------------------------------------------------------------------
        10 This is test
        20 CC DD FF
        27 Alex
        27 JRowbottom
        30 DD FF GG DD HH II
        36 Blue Advantage Option I
        40 DD FF GG DD HH II MM
        50 HH II MM
        70 AA BB

9 rows selected.

SQL> 

But my required output is,

        ID NAME
---------- -------------------------------------------------------
        20 CC DD FF
        30 DD FF GG DD 
        36 Blue Advantage Option
        40 DD FF GG DD HH II MM
        50 HH II MM
        70 AA BB

-->10 should not come because it has mismatch in the groups.
--> 27 should not come because it has mismatch in the groups.
-->40, 50 should come b'cos it has single value in the group



is it clear??

Thanks,
Thangam
Re: How to find the common values among groups of values [message #190189 is a reply to message #190180] Tue, 29 August 2006 11:08 Go to previous messageGo to next message
gold_oracl
Messages: 129
Registered: July 2006
Location: Westborough, MA
Senior Member
i have to insert this result set(id, name) into other table. so even if it is procedure also, it is ok for me.

any idea?

Re: How to find the common values among groups of values [message #190293 is a reply to message #190189] Wed, 30 August 2006 01:49 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Why is the required result for id 30 not 'DD FF GG DD HH II',as this is the common element between all the strings with id 30?
Re: How to find the common values among groups of values [message #190329 is a reply to message #190293] Wed, 30 August 2006 04:16 Go to previous messageGo to next message
gold_oracl
Messages: 129
Registered: July 2006
Location: Westborough, MA
Senior Member
oops. yes the value should be 'DD FF GG DD HH II' for 30.

to be more clear please check below

 SQL> desc  tab2;
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 FORMULARY_ID                                                   VARCHAR2(8)
 PLAN_NAME                                                      VARCHAR2(50)

SQL> select * from tab2 order by 1;

FORMULAR PLAN_NAME
-------- --------------------------------------------------
10       This is test
10       This is test for Oracle
10       This is test event
10       that is test
20       CC DD FF
20       CC DD FF GG HH II JJ
20       CC DD FF II
22       The size is 80
22       The size was 80
22       The size will 80
27       JRowbottom
27       Alex
30       DD FF GG DD HH II
30       DD FF GG DD HH II MM
36       Blue Advantage Option II
36       Blue Advantage Option I
36       Blue Advantage Option III
40       DD FF GG DD HH II MM
50       HH II MM
70       AA BB CC
70       AA BB
70       AA BB DD

22 rows selected.

My Requiered output is,

20       CC DD FF
22       The size
30       DD FF GG DD HH II
36       Blue Advantage Option
40       DD FF GG DD HH II MM
50       HH II MM
70       AA BB


The conditions are,
1) if there is single row in the group then it should be in the ouput(40,50)
2) if first word in the names in groups are diffent, then record should not come for this id(27,10)
3) If the first 2 or more words across all the plan names are the same, then the common name in the group should be displayed(the matching should be left to right)-20,22,30 etc

i'm looking forward for either direct SQL or PL/SQL. the final result should be inserted into other table.
So anything is ok for me.

Thanks,
Thangam
Re: How to find the common values among groups of values [message #190338 is a reply to message #190329] Wed, 30 August 2006 05:17 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Ok, here we are, using the same table definition as before

delete temp_instr;

insert into temp_instr values (10       ,'This is test');
insert into temp_instr values (10       ,'This is test for Oracle');
insert into temp_instr values (10       ,'This is test event');
insert into temp_instr values (10       ,'that is test');
insert into temp_instr values (20       ,'CC DD FF');
insert into temp_instr values (20       ,'CC DD FF GG HH II JJ');
insert into temp_instr values (20       ,'CC DD FF II');
insert into temp_instr values (22       ,'The size is 80');
insert into temp_instr values (22       ,'The size was 80');
insert into temp_instr values (22       ,'The size will 80');
insert into temp_instr values (27       ,'JRowbottom');
insert into temp_instr values (27       ,'Alex');
insert into temp_instr values (30       ,'DD FF GG DD HH II');
insert into temp_instr values (30       ,'DD FF GG DD HH II MM');
insert into temp_instr values (36       ,'Blue Advantage Option II');
insert into temp_instr values (36       ,'Blue Advantage Option I');
insert into temp_instr values (36       ,'Blue Advantage Option III');
insert into temp_instr values (40       ,'DD FF GG DD HH II MM');
insert into temp_instr values (50       ,'HH II MM');
insert into temp_instr values (70       ,'AA BB CC');
insert into temp_instr values (70       ,'AA BB');
insert into temp_instr values (70       ,'AA BB DD');

select id
      ,max(bit) keep (dense_rank last order by length(bit))
from (select t1.id
      ,t1.col_1
      ,t2.bit
      ,count(distinct t1.rowid) over (partition by t1.id) t1_rows
      ,sum(decode(t2.bit,substr(t1.col_1,1,length(t2.bit)),1,0)) over (partition by t1.id,t1.col_1,t2.bit) total_matches
from   temp_instr t1
      ,(select id
              ,col_1
              ,trim(substr(col_1,1,decode(instr(col_1,' ',1,r.lvl),0,length(col_1),instr(col_1,' ',1,r.lvl)))) bit
        from   temp_instr, (select level lvl from dual connect by level < 100) r
        where  r.lvl <= length(col_1)- length(replace(col_1,' ',''))+1) t2
where  t1.id = t2.id)
where t1_rows = total_matches
group by id;


The inner SELECT labeled t2 gets all the distinct word chunks from COL_1 (called bit)
The next query out counts how many rows there are for this id (t1_rows) and how many of those rows are matched by this bit.
The outermost query gets the longest bit which matches all the rows for this id.
Re: How to find the common values among groups of values [message #190375 is a reply to message #190338] Wed, 30 August 2006 07:18 Go to previous messageGo to next message
gold_oracl
Messages: 129
Registered: July 2006
Location: Westborough, MA
Senior Member
JRowbottom,
thanks you very much.

Thanks,
Thangam
Re: How to find the common values among groups of values [message #190395 is a reply to message #190375] Wed, 30 August 2006 08:39 Go to previous messageGo to next message
gold_oracl
Messages: 129
Registered: July 2006
Location: Westborough, MA
Senior Member
JRowbottom,
thanks for all your reply.
this is not giving the common plan name among the group if all the plan name are same.

for example,

11 good bye
11 good bye

here 'good bye' are same among the id 11.
so this should come in the output as

11 good bye

any idea??

Thanks,
Thangam
Re: How to find the common values among groups of values [message #190398 is a reply to message #190395] Wed, 30 August 2006 09:09 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Tho only fix I can find is to change
where t1_rows = total_matches
to
where t1_rows <= total_matches
Re: How to find the common values among groups of values [message #190403 is a reply to message #190398] Wed, 30 August 2006 09:24 Go to previous message
gold_oracl
Messages: 129
Registered: July 2006
Location: Westborough, MA
Senior Member
Thanks JRowbottom.
Previous Topic: table function in anather function
Next Topic: how to import/copy the tables of one user to another user.
Goto Forum:
  


Current Time: Fri Dec 02 20:41:02 CST 2016

Total time taken to generate the page: 0.06245 seconds