CODEWITHSUNDEEP
sqlsql-servert-sql
CallumDA
CallumDA

Reputation: 12113

Select based on specific condition

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

Upvotes: 3

Views: 114

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

Originally, I completely misunderstood the question. I see, you want a prioritization query. One simple method is:

select e.*
from @example e
where e.TableSensitivity like '%Sensitivity'
union all
select e.*
from @example e
where e.TableSensitivity like '%Revision' and
      not exists (select 1
                  from @example e2
                  where e2.tableId = e.tableId and
                        e2.TableSensitivity like '%Sensitivity'
                 );

A more general approach uses row_number():

select e.*
from (select e.*,
             row_number() over (partition by tableId
                                order by case when e.TableSensitivity like '%Sensitivity' then 1 when e.TableSensitivity like '% Revision' then 2 else 3 end
                               ) as seqnum
      from @example e
     ) e
where seqnum = 1;

Upvotes: 0

J.Loscos
J.Loscos

Reputation: 2907

This can also be achieved using the FIRST_VALUE function (for SQL Server 2012 and over)

SELECT DISTINCT 
    TableID, 
    FIRST_VALUE(TableSensitivity) OVER (PARTITION BY TableID ORDER BY 
            CASE WHEN  TableSensitivity LIKE '%Sensitivity' THEN 1 
            WHEN TableSensitivity LIKE '%Revision' THEN 2 
            ELSE 3 END) as TableSensitivity
FROM @Example

Upvotes: 1

Junc Mayl
Junc Mayl

Reputation: 101

this gets rid of the rows in @Example and returns your expected result. it can be modified and worked into your query if needed:

DELETE
FROM    @Example
WHERE   TableID IN
        (
            SELECT  TableID
            FROM    @Example
            WHERE   TableSensitivity LIKE '%Sensitivity%'
        )
        AND TableSensitivity LIKE '%Revision%'

SELECT  *
FROM    @Example

Upvotes: 2

Simon
Simon

Reputation: 1081

You could use a WHERE NOT EXISTS

SELECT TableID
     , TableSensitivity
FROM @Example e1
WHERE NOT EXISTS
(
    SELECT *
    FROM @Example e2
    WHERE e1.TableID=e2.TableID
    AND e2.TableSensitivity LIKE '%Sensitivity'
    AND e1.TableSensitivity LIKE '%Revision'
)

Upvotes: 3

Related Questions