sqlpostgresqldenodo

SQL getting rows with a flag value without using functions

I have a following table

enter image description here

I need to only select all the values with sensorfield1 flag = r and reject the rows with corresponding IDs with flag = h

and select those values for IDs where flag value is only 'h'

I am working on denodo and somehow can not use any agg/window functions because it throws error "(function) is not executable" error. So a solution with no utilization of function would suffice my needs.

Thanks!

required output would be enter image description here

Solution

  • You may try correlated subquery with exists clause as the following:

    Select sensorID, timestamp, sensorField1 
From tbl_name T
Where T.sensorField1 = 'r'
Or
(T.sensorField1 = 'h' And Not Exists (Select 1 From tbl_name D
                                      Where D.sensorID = T.sensorID 
                                      And D.sensorField1 = 'r')
)

    See a demo from db<>fiddle.