4 Replies Latest reply on Jan 22, 2013 2:18 PM by AlbertoFaenza

# extract set of records from group

Hi All,

I have total 6 sets of codes from which i have to select a pair of records ('NAREN','SUREN'). Which ever code has these two sets of records associated that records should be
pulled.

Oracle version installed: 10.0.1

sample dataset:

WITH T AS
(
SELECT 1 ID,1 CD,'NAREN' NM FROM DUAL
UNION
SELECT 2 ID,1 CD,'SUREN' NM FROM DUAL
UNION
SELECT 3 ID,1 CD,'CHITTI' NM FROM DUAL
UNION
SELECT 4 ID,1 CD,'ABHI' NM FROM DUAL
UNION
SELECT 5 ID,2 CD,'VENKAT' NM FROM DUAL
UNION
SELECT 6 ID,2 CD,'RAJ' NM FROM DUAL
UNION
SELECT 7 ID,2 CD,'MAHESH' NM FROM DUAL
UNION
SELECT 8 ID,2 CD,'NAREN' NM FROM DUAL
UNION
SELECT 9 ID,3 CD,'NAREN' NM FROM DUAL
UNION
SELECT 10 ID,3 CD,'SUREN' NM FROM DUAL
UNION
SELECT 11 ID,4 CD,'SUREN' NM FROM DUAL
UNION
SELECT 12 ID,5 CD,'PATIL' NM FROM DUAL
UNION
SELECT 13 ID,6 CD,'RAJESH' NM FROM DUAL
UNION
SELECT 14 ID,6 CD,'MALLI' NM FROM DUAL
UNION
SELECT 15 ID,6 CD,'SANTHI' NM FROM DUAL
UNION
SELECT 16 ID,6 CD,'PRAVEENA' NM FROM DUAL
)
SELECT * FROM T

Output required:
==============

ID     CD     NAME
1     1     NAREN
2     1     SUREN
9     3     NAREN
10     3     SUREN

Note: condition should match the set which exactly matches two values or (pair).

Thanks,
Naren
• ###### 1. Re: extract set of records from group
``````SELECT a.* FROM t a join t b
ON (  a.nm ='NAREN' and a.cd=b.cd and b.nm='SUREN'
OR a.nm ='SUREN' and a.cd=b.cd and b.nm='NAREN')
ORDER by a.id;

ID         CD NM
---------- ---------- --------
1          1 NAREN
2          1 SUREN
9          3 NAREN
10          3 SUREN``````
Regards.
Al
• ###### 2. Re: extract set of records from group
``````WITH T AS
(
SELECT 1 ID,1 CD,'NAREN' NM FROM DUAL
UNION
SELECT 2 ID,1 CD,'SUREN' NM FROM DUAL
UNION
SELECT 3 ID,1 CD,'CHITTI' NM FROM DUAL
UNION
SELECT 4 ID,1 CD,'ABHI' NM FROM DUAL
UNION
SELECT 5 ID,2 CD,'VENKAT' NM FROM DUAL
UNION
SELECT 6 ID,2 CD,'RAJ' NM FROM DUAL
UNION
SELECT 7 ID,2 CD,'MAHESH' NM FROM DUAL
UNION
SELECT 8 ID,2 CD,'NAREN' NM FROM DUAL
UNION
SELECT 9 ID,3 CD,'NAREN' NM FROM DUAL
UNION
SELECT 10 ID,3 CD,'SUREN' NM FROM DUAL
UNION
SELECT 11 ID,4 CD,'SUREN' NM FROM DUAL
UNION
SELECT 12 ID,5 CD,'PATIL' NM FROM DUAL
UNION
SELECT 13 ID,6 CD,'RAJESH' NM FROM DUAL
UNION
SELECT 14 ID,6 CD,'MALLI' NM FROM DUAL
UNION
SELECT 15 ID,6 CD,'SANTHI' NM FROM DUAL
UNION
SELECT 16 ID,6 CD,'PRAVEENA' NM FROM DUAL
)
, groups as (
select
cd
from (
select cd, nm from t
where
nm in ('NAREN','SUREN')
)
group by cd
having
(count(distinct nm) >=2)
)

select
*
from t
where
cd in
(select cd from groups    )
and
nm in ('NAREN','SUREN')

ID     CD     NM
1     1     NAREN
2     1     SUREN
9     3     NAREN
10     3     SUREN``````
• ###### 4. Re: extract set of records from group
Another alternative:
``````WITH T AS
(
SELECT 1 ID,1 CD,'NAREN' NM FROM DUAL
UNION
SELECT 2 ID,1 CD,'SUREN' NM FROM DUAL
UNION
SELECT 3 ID,1 CD,'CHITTI' NM FROM DUAL
UNION
SELECT 4 ID,1 CD,'ABHI' NM FROM DUAL
UNION
SELECT 5 ID,2 CD,'VENKAT' NM FROM DUAL
UNION
SELECT 6 ID,2 CD,'RAJ' NM FROM DUAL
UNION
SELECT 7 ID,2 CD,'MAHESH' NM FROM DUAL
UNION
SELECT 8 ID,2 CD,'NAREN' NM FROM DUAL
UNION
SELECT 9 ID,3 CD,'NAREN' NM FROM DUAL
UNION
SELECT 10 ID,3 CD,'SUREN' NM FROM DUAL
UNION
SELECT 11 ID,4 CD,'SUREN' NM FROM DUAL
UNION
SELECT 12 ID,5 CD,'PATIL' NM FROM DUAL
UNION
SELECT 13 ID,6 CD,'RAJESH' NM FROM DUAL
UNION
SELECT 14 ID,6 CD,'MALLI' NM FROM DUAL
UNION
SELECT 15 ID,6 CD,'SANTHI' NM FROM DUAL
UNION
SELECT 16 ID,6 CD,'PRAVEENA' NM FROM DUAL
)
, t1 as
(
SELECT id, cd, nm, COUNT(DISTINCT nm) OVER(PARTITION BY cd) cnt
FROM T
WHERE nm IN ('NAREN', 'SUREN')
)
select id, cd, nm
from t1
WHERE cnt > 1;

ID         CD NM
---------- ---------- --------
1          1 NAREN
2          1 SUREN
9          3 NAREN
10          3 SUREN   ``````
Regards.
Al

Edited by: Alberto Faenza on Jan 22, 2013 3:17 PM
Modified count(*) with COUNT(DISTINCT nm)