CODEWITHSUNDEEP
sqldatabasepostgresqlselect
rshar
rshar

Reputation: 1475

Group by and delete rows based on conditions

I have following table in postgres.

col1    col2             col3
1       Other            a
2       Drug             b    
1       Procedure        c
3       Combination Drug d
4       Biological       e
3       Behavioral       f
3       Drug             g
5       Drug             g
6       Procedure        h

I would like to select rows based on following condition.

select * from table where col2 in ('Other', 'Drug', 'Combination Drug', 'Biological')
order by col1

This is giving me below output.

col1    col2             col3
1       Other            a
2       Drug             b    
3       Combination Drug d
3       Drug             g
4       Biological       e
5       Drug             g

But the above filter excluding the below rows and is including rows with col1 ids (1, 3) which are associated with 'Procedure' and 'Behavioral'

1       Procedure        c
3       Behavioral       f

However, I would like to also exclude the other rows which are associated with them

1       Other            a
3       Combination Drug d
3       Drug             g

I am unable to find a solution to this problem. Any help is highly appreciated. Thanks

Upvotes: 1

Views: 165

Answers (1)

GMB
GMB

Reputation: 222622

I think that you are looking for not exists:

select t.*
from mytable t
where not exists (
    select 1 
    from mytable t1
    where t1.col1 = t.col1 and t1.col2 not in ('Other', 'Drug', 'Combination Drug', 'Biological')
)

Demo on DB Fiddle:

col1 | col2       | col3
---: | :--------- | :---
   2 | Drug       | b   
   4 | Biological | e   
   5 | Drug       | g

You could also use window functions:

select (t.t).*
from (
    select 
        t,
        bool_and(col2 in ('Other', 'Drug', 'Combination Drug', 'Biological'))
            over(partition by col1) flag
    from mytable t
) t
where flag

Upvotes: 2

Related Questions