Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Query to Identify Sets Having Given Pattern

644850Jun 16 2008 — edited Jun 16 2008
Hi,

Assume that i have following table

Id Code
1 aaa
1 bbb
1 ccc
2 aaa
2 bbb
2 ddd
3 bbb
3 eee

The records will be grouped by Id. I want to identify sets having following criteria
1. Set should have at least one record with code like 'a%'
and should have at least one record for codes 'bbb', 'ccc' (in this case group 1 would be selected)
2. Set should have at least one record with code like 'a%'
and should have at least one record for either 'bbb' or 'ccc' (in this case group 1and 2 would be selected)

Does anyone have idea about how this could be achieved efficiently? Any pointers, references would be appreciated

Regards
~Pravin.

Comments

jeneesh

Problem 1 Can be solved like:

SQL> select * from t;

        ID CODE
---------- ----------
         1 aaa
         1 bbb
         1 ccc
         2 aaa
         2 bbb
         2 ddd
         3 bbb
         3 eee

8 rows selected.

SQL> select id
  2  from t
  3  group by id
  4  having sum(case when code like 'a%' then 1 else 0 end) > 0
  5  and sum(decode(code,'bbb',1,0)) > 0
  6  and sum(decode(code,'ccc',1,0)) > 0;

        ID
----------
         1

<br>
<br>

Similarly Problem 2 can also be solved...                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
644850
Hi Jeneesh,

Thanks for your solution.

Regards
~Pravin
Aketi Jyuuzou
create table T(ID,Code) as
select 1,'aaa' from dual union
select 1,'bbb' from dual union
select 1,'ccc' from dual union
select 2,'aaa' from dual union
select 2,'bbb' from dual union
select 2,'ddd' from dual union
select 3,'bbb' from dual union
select 3,'eee' from dual;

1. Set should have at least one record with code like 'a%'
and should have at least one record for codes 'bbb', 'ccc'
(in this case group 1 would be selected)

select ID
  from T
group by ID
having max(case when code like 'a%' then 1 else 0 end)
     * max(case when code = 'bbb' then 1 else 0 end)
     * max(case when code = 'ccc' then 1 else 0 end) = 1;
ID
--
 1

2. Set should have at least one record with code like 'a%'
and should have at least one record for either 'bbb' or 'ccc'
(in this case group 1and 2 would be selected)

select ID
  from T
group by ID
having  max(case when code like 'a%' then 1 else 0 end)
           * (max(case when code = 'bbb' then 1 else 0 end)
            + max(case when code = 'ccc' then 1 else 0 end)) >= 1;
ID
--
 1
 2

or

select ID
  from T
group by ID
having max(case when code like 'a%'        then 1 else 0 end)
     * max(case when code in ('bbb','ccc') then 1 else 0 end) = 1;

similar threads
551061
550921
526715
551338

Frank Kulash

Hi, Pravin,

If efficiency is important, then you should do one test directly (preferably the one that rules out the most groups), and use corellated EXISTS subqueries for the other tests.
For example, to find ids with 'a%' and both 'bbb' and 'ccc':

SELECT DISTINCT	id
FROM	t	m	-- m for main
WHERE	code	= 'ccc'
AND	EXISTS
	(	-- Begin subquery to find 'a%'
	SELECT	NULL
	FROM	t
	WHERE	id	= m.id
	AND	code	LIKE 'a%'
	)	-- End subquery to find 'a%'
AND	EXISTS
	(	-- Begin subquery to find 'bbb'
	SELECT	NULL
	FROM	t
	WHERE	id	= m.id
	AND	code	= 'bbb'
	)	-- End subquery to find 'bbb'
;
635471
A couple more options, especially useful when you want to return the complete data set without grouping, but worth benchmarking otherwise anyway.
select id,code
from
(
select id,
       code,
       sum(case when code like 'a%' then 1 else 0 end) over (partition by id) num_a,
       sum(case code when 'bbb'     then 1 else 0 end) over (partition by id) num_bbb,
       sum(case code when 'ccc'     then 1 else 0 end) over (partition by id) num_ccc
from   t
)
where num_a >= 1 and
      num_bbb >= 1 and
      num_ccc >= 1
/
      
select id,code
from
(
select id,
       code,
       sum(case when code like 'a%' then 1 else 0 end) over (partition by id) num_a,
       sum(case code when 'bbb'     then 1 else 0 end) over (partition by id) num_bbb,
       sum(case code when 'ccc'     then 1 else 0 end) over (partition by id) num_ccc
from   t
)
where num_a >= 1 and
      (num_bbb >= 1 or
       num_ccc >= 1)
/
 
1 - 5
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jul 14 2008
Added on Jun 16 2008
5 comments
1,406 views