Question 115061 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – 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

Tags:

3

Answers


  1. 0

    Try this query:

    SELECT sensorID, timestamp, sensorField1 
    FROM table1
    WHERE sensorField1 = 'r'
UNION ALL
SELECT sensorID, timestamp, sensorField1 
    ROM table1
    WHERE sensorField1 = 'h' 
    AND sensorID NOT IN (SELECT DISTINCT sensorID FROM table1 sensorField1 = 'r')
    Login or Signup to reply.
  2. 0

    {imparted}

    Hi,

    VDP does not implement Analytic Functions itself but delegates such functions to the data source, if applicable. For instance, not all data sources support such analytics functions. Thus, when a data source does not support this function this error is thrown.

    Thus, I’d recommend to check your Connection tab and make sure you selected “The against adptr ( for example "Oracle 11g")” as database adapter. Further, I’d recommend to check your Data Source Configuration if the Delegate analytic functions list is set (unchecked is fine, as default is all functions get delegated if the underlying data source support this).

    Hope this helps!

    Login or Signup to reply.
  3. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search