CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2017
Eric Klaus
Eric Klaus

Reputation: 955

Select rows based on field values

I have this structure

CREATE TABLE #Test
(
    Field1   VARCHAR(10),
    Field2   VARCHAR(10)
)

INSERT #Test VALUES('AAA','NO'),
                   ('BBB','NO'),
                   ('BBB','YES'),
                   ('BBB','SURE'),
                   ('CCC','NO'),
                   ('DDD','NO'),
                   ('DDD','YES'),
                   ('DDD','ALWAYS')

SELECT * FROM #Test

Every distinct Field1 value has Field2 'NO'. I want to select all rows from #Test such that if distinct Field1 value has multiple Field2 values, return everything except the row with Field2='NO', otherwise return with 'NO' value. So result should be this

enter image description here

AAA has only 'NO', so we return it, BBB has multiple, so return everything except 'NO'.

I want to select it in one statement, without deleting , etc.. I tried ROW_NUMBER() partitioned by Field1, but that doesn't solve the issue.

Upvotes: 2

Views: 911

Answers (3)

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

You can use window function : 

SELECT t.Field1, t.Field2 
FROM (SELECT t.*, 
             SUM(CASE WHEN FIELD2 = 'NO' THEN 1 ELSE 0 END) OVER (PARTITION BY FIELD1) AS NO_FLAG,
             COUNT(*) OVER (PARTITION BY FIELD1) AS CNT
      FROM Test t
      ) t
WHERE (CNT <> NO_FLAG AND FIELD2 <> 'NO') OR
      (CNT = NO_FLAG AND FIELD2 = 'NO');

Here is demo.

Upvotes: 0

MrKnino
MrKnino

Reputation: 26

I hope this example helps you.

select Field1,Field2 from #Test where Field1 not in(SELECT Field1 FROM #Test where Field2<>'NO')
UNION ALL
SELECT Field1,Field2 FROM #Test where Field2<>'NO'
order by Field1

Upvotes: 0

forpas
forpas

Reputation: 164174

With NOT EXISTS:

SELECT * FROM #Test t
WHERE t.Field2 <> 'NO'
OR NOT EXISTS (SELECT 1 FROM #Test WHERE Field1 = t.Field1 AND Field2 <> 'NO')

See the demo.
Results:

> Field1 | Field2
> :----- | :-----
> AAA    | NO    
> BBB    | YES   
> BBB    | SURE  
> CCC    | NO    
> DDD    | YES   
> DDD    | ALWAYS

Upvotes: 1

Related Questions