Non null value associated with least value in other column

admin

3 years ago

My data is like this:

Desired output:

I have tried using following SQL:

CASE 
    WHEN (MINDAY_DIFF > 0) AND (MINDAY_DIFF IS NOT NULL) 
        THEN FIRST_VALUE(BP_MED) OVER (PARTITION BY ID ORDER BY MINDAY_DIFF ASC) 
END AS DRUG

This returns NULL.

I also tried

CASE 
    WHEN (MINDAY_DIFF > 0) 
        THEN BP_MED 
        ELSE NULL 
END AS DRUG

It returns both non-null values of BP_MED.

I also tried NVL but that didn’t work either.

Since it is in Netezza. There are fewer solutions online. Please help.