Oracle filter groups that contains more than one row condition-CodePudding

I need a SQL query for Oracle that select groups that contains the elements "ABC" and "ANQ"

group X   Column Q
--------   ---------
123        ABC
123        AAA    
123        ANQ
456        ANQ
456        PKR
579        AAA
579        XYZ
886        ABC

The desired result should be

 group X   Column Q
    --------   ---------
    123        ABC
    123        AAA    
    123        ANQ

CodePudding user response：

You can query the table only once by using the analytic COUNT function with conditional aggregation:

SELECT x,
       q
FROM   (
  SELECT x,
         q,
         COUNT(CASE q WHEN 'ABC' THEN 1 END) OVER (PARTITION BY x) AS num_abc,
         COUNT(CASE q WHEN 'ANQ' THEN 1 END) OVER (PARTITION BY x) AS num_anq
  FROM   table_name
)
WHERE  num_abc > 0
AND    num_anq > 0;

Which, for the sample data:

CREATE TABLE table_name (X, Q) AS
SELECT 123, 'ABC' FROM DUAL UNION ALL
SELECT 123, 'AAA' FROM DUAL UNION ALL
SELECT 123, 'ANQ' FROM DUAL UNION ALL
SELECT 456, 'ANQ' FROM DUAL UNION ALL
SELECT 456, 'PKR' FROM DUAL UNION ALL
SELECT 579, 'AAA' FROM DUAL UNION ALL
SELECT 579, 'XYZ' FROM DUAL UNION ALL
SELECT 886, 'ABC' FROM DUAL;

Outputs:

X	Q
123	ABC
123	AAA
123	ANQ

fiddle

CodePudding user response：

For example:

Sample data:

SQL> with test (x, q) as
  2    (select 123, 'abc' from dual union all
  3     select 123, 'aaa' from dual union all
  4     select 123, 'anq' from dual union all
  5     select 456, 'anq' from dual union all
  6     select 456, 'pkr' from dual union all
  7     select 579, 'aaa' from dual union all
  8     select 579, 'xyz' from dual union all
  9     select 886, 'abc' from dual
 10    )

Query:

 11  select x, q
 12  from test a
 13  where exists (select null
 14                from test b
 15                where b.q in ('abc', 'anq')
 16                  and b.x = a.x
 17                group by b.x
 18                having count(distinct b.q) = 2
 19               );

         X Q
---------- ---
       123 abc
       123 aaa
       123 anq

SQL>