Remove duplicate value available in all order

Question

I have a table with values as shown below

Sno	orderno	partno
101	OCT1	oRing
101	OCT1	Gasket
101	OCT1	Case
101	OCT2	gRing
101	OCT2	Gasket
101	OCT2	Cover
101	OCT3	Case
101	OCT3	Gasket
102	OCT1	Gasket

I want to remove the values that are common in all orders (OCT1,OCT2,OCT3) per Sno. Output should also include orderno as well something like below . Here Gasket is available in all order for 101, so i want to remove it but Case is used in only 2 order out of 3, so i cannot remove it.

Sno	orderno	partno
101	OCT1	oRing
101	OCT1	Case
101	OCT2	gRing
101	OCT2	Cover
101	OCT3	Case
102	OCT1	Gasket

I have tried below code but not working. Please help/suggest

select * from mytable where partno not in (
select max(partno) from mytable
group by sno
having count(orderno)=count(partno))

Answer 1

We can try the following approach. The first CTE finds the distinct number of orders within each Sno. We then also find, in the second CTE, the number of times a product appears within a given Sno. A matching record is one for which the product did not appear in every order.

WITH cte1 AS (
    SELECT Sno, COUNT(DISTINCT orderno) AS ordernocnt
    FROM mytable
    GROUP BY Sno
),
cte2 AS (
    SELECT t1.*, t2.ordernocnt,
           COUNT(*) OVER (PARTITION BY Sno, partno) partcnt
    FROM mytable t1
    INNER JOIN cte1 t2 ON t2.Sno = t1.Sno
)

SELECT Sno, orderno, partno
FROM cte2
WHERE partcnt < ordernocnt OR ordernocnt = 1
ORDER BY Sno, orderno;