Select rows where partition meets condition (PostgreSQL)

Question

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!

Gordon Linoff · Accepted Answer · 2019-12-19 15:17:45Z

2

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'
                 );

answered Dec 19, 2019 at 15:17

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

jcf Over a year ago

When I tried this solution, it worked, but if I put a value in the condition that does not exists in the table (for example, instead of t2.colb = 'NOK', using t2.colb = 'AK'), the result was an empty table.

Gordon Linoff Over a year ago

@jcf . . . It should return all rows if you put in a value that is not in the table.

Laurenz Albe · Accepted Answer · 2019-12-19 15:31:26Z

1

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;

answered Dec 19, 2019 at 15:31

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Collectives™ on Stack Overflow

Select rows where partition meets condition (PostgreSQL)

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related