By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE x ( Order_no, Type ) AS
SELECT 123, 'A' FROM DUAL UNION ALL
SELECT 123, 'B' FROM DUAL UNION ALL
SELECT 123, 'C' FROM DUAL UNION ALL
SELECT 234, 'A' FROM DUAL UNION ALL
SELECT 234, 'C' FROM DUAL UNION ALL
SELECT 345, 'B' FROM DUAL UNION ALL
SELECT 456, 'C' FROM DUAL UNION ALL
SELECT 456, 'A' FROM DUAL UNION ALL
SELECT 567, 'A' FROM DUAL UNION ALL
SELECT 567, 'B' FROM DUAL;
10 rows affected
SELECT order_no,
type
FROM (
SELECT x.*,
COUNT( CASE type WHEN 'A' THEN 1 END ) OVER ( PARTITION BY order_no )
AS num_a,
COUNT( CASE type WHEN 'B' THEN 1 END ) OVER ( PARTITION BY order_no )
AS num_b
FROM x
)
WHERE num_a > 0
AND num_b > 0;
| ORDER_NO | TYPE |
|---|---|
| 123 | A |
| 123 | B |
| 123 | C |
| 567 | A |
| 567 | B |