CODEWITHSUNDEEP
sqlpostgresqlgrouping
jcf
jcf

Reputation: 602

Select rows where partition meets condition (PostgreSQL)

I could not find a similar question with these characteristics, if it is duplicated I'll close the question.

Imagine I have the following table in a PostgreSQL DB:

  | serverdate | colA | colB | colC | colD
  |-------------------------------------------
0 | 2019-12-01 |    a | OK   | 10   | string1
1 | 2019-12-02 |    a | OK   | 10   | string2
2 | 2019-12-02 |    a | NOK  | 100  | string3
3 | 2019-12-01 |    b | OK   | 0    | string1
4 | 2019-12-03 |    b | OK   | 1    | string1
5 | 2019-12-05 |    c | NOK  | 0    | string2
5 | 2019-12-07 |    d | OK   | 10   | string3
5 | 2019-12-08 |    d | EX   | 1000 | string4
5 | 2019-12-12 |    e | OK   | 1    | string5

I would like to select all the rows where the following condition applies (I don't know how to phrase this condition exactly, an example is way clearer) :

For all the groups in colA, check if there is a NOK in colB. If there is any NOK in the grouping, do not select any row of this group

For example, for the mentioned table, the result would be:

  | serverdate | colA | colB | colC | colD
  |-------------------------------------------
3 | 2019-12-01 |    b | OK   | 0    | string1
4 | 2019-12-03 |    b | OK   | 1    | string1
5 | 2019-12-07 |    d | OK   | 10   | string3
5 | 2019-12-08 |    d | EX   | 1000 | string4
5 | 2019-12-12 |    e | OK   | 1    | string5

Any ideas on how to do this query? Simplicity would be advised.

Thanks in advance!

Upvotes: 0

Views: 1078

Answers (2)

Laurenz Albe
Laurenz Albe

Reputation: 246513

Perhaps a window function can help:

SELECT cola, colb, ...
FROM (SELECT cola, colb, ...,
             bool_or(colb = 'NOK')
                OVER (PARTITION BY cola) AS hasnok
      FROM tab) AS q
WHERE NOT hasnok;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269773

NOT EXISTS is a canonical solution:

select t.*
from t
where not exists (select 1
                  from t t2
                  where t2.cola = t.cola and t2.colb = 'NOK'
                 );

Upvotes: 2

Related Questions