Oracle SQL: How to filter out rows based on different row values

Question

I want to filter my data, based on the following condtion

• For an ID, if a part appears twice or more than twice under the specific Types A and B, then I want to filter the data on that.

data

| ID  | TYPE | PART |
|-----|------|------|
| 101 | A    | 10   |
| 101 | B    | 10   |
| 101 | B    | 10   |
| 101 | B    | 20   |
| 101 | C    | 30   |
| 102 | A    | 10   |
| 102 | B    | 25   |
| 103 | A    | 25   |
| 103 | B    | 25   |

output

| ID  | Type | Part |
|-----|------|------|
| 101 | A    | 10   |
| 101 | B    | 10   |
| 101 | B    | 10   |
| 103 | A    | 25   |
| 103 | B    | 25   |

WITH data AS (
    SELECT 101 id, 'A' type, 10 part FROM dual UNION ALL
    SELECT 101 id, 'B' type, 10 part FROM dual UNION ALL
    SELECT 101 id, 'B' type, 10 part FROM dual UNION ALL
    SELECT 101 id, 'B' type, 20 part FROM dual UNION ALL
    SELECT 101 id, 'C' type, 30 part FROM dual UNION ALL
    SELECT 102 id, 'A' type, 10 part FROM dual UNION ALL
    SELECT 102 id, 'B' type, 25 part FROM dual UNION ALL
    SELECT 103 id, 'A' type, 25 part FROM dual UNION ALL
    SELECT 103 id, 'B' type, 25 part FROM dual
)
SELECT * FROM data;

another solution

WITH data AS (
    SELECT 101 id, 'A' type, 10 part FROM dual UNION ALL
    SELECT 101 id, 'B' type, 10 part FROM dual UNION ALL
    SELECT 101 id, 'B' type, 10 part FROM dual UNION ALL
    SELECT 101 id, 'B' type, 20 part FROM dual UNION ALL
    SELECT 101 id, 'C' type, 30 part FROM dual UNION ALL
    SELECT 102 id, 'A' type, 10 part FROM dual UNION ALL
    SELECT 102 id, 'B' type, 25 part FROM dual UNION ALL
    SELECT 103 id, 'A' type, 25 part FROM dual UNION ALL
    SELECT 103 id, 'B' type, 25 part FROM dual UNION ALL
    SELECT 104 id, 'C' type, 30 part FROM dual UNION ALL
    SELECT 104 id, 'D' type, 30 part FROM dual
), data2 AS (
    SELECT data.*, COUNT(DISTINCT type) OVER (PARTITION BY id, part) cnt
    FROM data
    WHERE type IN ('A','B')
)
SELECT id, type, part
FROM data2
WHERE cnt > 1;

Answer 1

try something like this: (I fixed the 104/103 record here, assuming to match your initial data)

WITH data AS (
    SELECT 101 id, 'A' type, 10 part FROM dual UNION ALL
    SELECT 101 id, 'B' type, 10 part FROM dual UNION ALL
    SELECT 101 id, 'B' type, 10 part FROM dual UNION ALL
    SELECT 101 id, 'B' type, 20 part FROM dual UNION ALL
    SELECT 101 id, 'C' type, 30 part FROM dual UNION ALL
    SELECT 102 id, 'A' type, 10 part FROM dual UNION ALL
    SELECT 102 id, 'B' type, 25 part FROM dual UNION ALL
    SELECT 103 id, 'A' type, 25 part FROM dual UNION ALL
    SELECT 103 id, 'B' type, 25 part FROM dual
   ),
   w_cnt as
      ( SELECT id, part, count(*)
         FROM data
         group by id, part
         having count(*) > 1
       )
select *
from data   d,
     w_cnt   w
where d.id = w.id
  and d.part = w.part
/

        ID T       PART         ID       PART   COUNT(*)
---------- - ---------- ---------- ---------- ----------
       103 B         25        103         25          2
       103 A         25        103         25          2
       101 B         10        101         10          3
       101 B         10        101         10          3
       101 A         10        101         10          3

5 rows selected.