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: 
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'
)
Use `not exists` and `group by` as follows:

```
Select t.supplier_id, 'open' as order_status, max(order_date) as order_date
  From your_table t
 Where not exists
       (Select 1 from your_table tt
         Where tt.supplier_id = t.supplier_id
           And tt.order_status <> t.order_status)
  And t.order_status = 'open'
Group by t.supplier_id

```