*If someone could update the title to better reflect my question that would be much appreciated. I think I've struggled to find the answer somewhat because I don't know the right search terms.*

> 
> If I have the following values in a table:
> 
> 
> 
> ```
> TableID      TableSensitivity
> -----------------------------
> 1            aSensitivity
> 1            aRevision
> 2            bRevision
> 3            cRevision
> 3            cSensitivity
> 
> ```
> 
> How can I filter the values containing "Revision" *only* for each TableID where the is
>  already a value containing "Sensitivity". This is the expected output:
> 
> 
> 
> ```
> TableID      TableSensitivity
> -----------------------------
> 1            aSensitivity
> 2            bRevision
> 3            cSensitivity
> 
> ```
> 
> 

Here's an example setup:

```
DECLARE  @Example TABLE (TableID INT, TableSensitivity VARCHAR(200))

INSERT INTO @Example(TableID, TableSensitivity) VALUES (1, 'aSensitivity')
INSERT INTO @Example(TableID, TableSensitivity) VALUES (1, 'aRevision')
INSERT INTO @Example(TableID, TableSensitivity) VALUES (2, 'bRevision')
INSERT INTO @Example(TableID, TableSensitivity) VALUES (3, 'cSensitivity')
INSERT INTO @Example(TableID, TableSensitivity) VALUES (3, 'cRevision')

```

I've tried using CASE to determine the rows with sensitivities and figured that I could use a MAX to filter, but couldn't get it working:

```
SELECT TableID
     , TableSensitivity
     , CASE WHEN TableSensitivity LIKE '%Sensitivity' THEN 1 ELSE 0 END 'SomeKey' 
FROM @Example

```