Excluding Records using specific conditions-CodePudding

I have a table as below

ID	Type	Part
CD1	Service	A
CD1	Service	null
CD1	Service	B
CD1	Sales	A
CD2	Service	null
CD2	Sales	B
CD3	Service	A
CD3	Service	null

Output Required:

ID	Type	Part
CD1	Service	A
CD1	Service	B
CD1	Sales	A
CD2	Service	null
CD2	Sales	B
CD3	Service	A

Explanation : For example CD1 has Service as Type with A,B and a null as Part, CD2 has Service as Type with only null as Part. Since CD1 has A,B as part, null value record has to be excluded and CD2 with Service as Type doesn't contain any values other than null it should not be excluded.

Similarly CD3 has Service as Type with A and null as Part. Since A exists null value record has to be excluded.

Is this possible to achieve using SQL?

Thanks in Advance

CodePudding user response：

You can assign a ranking using the ROW_NUMBER window function, where the null values will be assigned the least value. Then you can select all rows from your table where the value is not null or the ranking is 1 (if null is found in first position, it will mean that it's the only value for that combination of ID and Type):

WITH cte AS (
    SELECT *, 
           ROW_NUMBER() OVER(PARTITION BY ID, Type 
                             ORDER     BY Part DESC) AS rn
    FROM tab
)
SELECT ID,
       Type, 
       Part
FROM cte
WHERE Part IS NOT NULL 
   OR rn = 1

Try it here.