sql - Apply condition within a group in Nifi

Question

How to get list of suppliers having only open orders? My sample data is as below:

SuppierID, orderID, orderStatus,orderdate
1,11,open,12/28/2020
1,22,open,12/27/2020
2,33,open,12/26/2020
2,44,closed,12/27/2020
3,55,closed,12/26/2020

Expected output is:

1,12/28/2020

Tried with groupby and having in query record processor but having clause not supported by Nifi it seems

Answer 1

You could try something like

SELECT DISTINCT T.SupplierID FROM YourTable AS T WHERE NOT EXISTS ( SELECT 1 FROM YourTable AS X WHERE T.SupplierID=X.SupplierID AND X.orderStatus='closed' )