SQL: How to filter rows in SQL based on existence of a 2 different column's value against one I-CodePudding

I have a table with following columns:

Row Number         |         ID         | Type
1.                           01             A
2.                           01             B
3.                           02             A
4.                           03             B

I want to filter the Tableased on the condition that for a given ID, if both type A and B exists, then it should keep only Type B but if it doesn't exists then it shouldn't do anything.

So in this case the resultant table would look like:

Row Number         |         ID         | Type
2.                           01             B
3.                           02             A
4.                           03             B

How can I achieve this ?

Where table the original table name is t

CodePudding user response：

You want to know if for Type A, Type B exists, which sounds like a use-case for exists

select *
from t
where not exists (
  select * from t t2 
  where t2.Id = t.Id and t2.Type = 'B' and t.Type = 'A'
);