SQL - Filter and group based on column value-CodePudding

I have the below table.

Code     Status    
1        Noaudit/One  
1        Audit/One      
1        Audit/Two      
2        Noaudit/One
2        Noaudit/Two
3        Audit/One
3        Audit/Two

This is the desired output result:

Code     Status      
1        Audit/One      
1        Audit/Two
2        Noaudit/One
2        Noaudit/Two
3        Audit/One
3        Audit/Two

Basically, if a code has status of audit and Noaudit only audit records should be displayed. If the code has only Noaudit records all the Noaudit records should be displayed. Thanks in advance.

I tried grouping by and then applying the filter but it did not work.

CodePudding user response：

You can do:

select t.*
from t
join (
  select code, 
    sum(t.status like 'A%' then 1 else 0 end) as a,
    sum(t.status like 'N%' then 1 else 0 end) as n
  from t group by code
) x on x.code = t.code
where x.a > 0 and x.n > 0 and t.status like 'A%'
   or x.a = 0 and x.n > 0 and t.status like 'N%'

CodePudding user response：

Try the following:

select Code, Status
from
(
 select *, 
    count(case when Status like 'Audit%' then 1 end) over (partition by Code) as Audit
 from table_name
) T
where Status like 'Audit%' or Audit=0

See a demo.