Window Function with a condition

Question

I have the below table.

Can I create a Window Function First_Value when a condition is met?

For example, I need the first value when is = 1 and partition by id

TableA

ID    Date       IS
 1     1/1/18    0
 1     1/2/18    1

My work:

   SELECT 
   CASE
   WHEN A.IS = 1 THEN A.DATE END)OVER (PARTITION BY A.ID ORDER BY A.DATE)                
   END FIRST_ATTEMPT_DT
   FROM TABLEA A

Answer 1

You may use in such a way like below :

   SELECT 
   (CASE
   WHEN A."is" = 1 THEN 
        First_Value("Date") OVER (PARTITION BY A.ID ORDER BY A."Date")  
   ELSE
        A."Date"
   END) as FIRST_ATTEMPT_DT
   FROM TABLEA A
   WHERE "is" = 1;

Where IS and DATE are preserved keywords in Oracle, and so they should be quoted.

P.S. It seems you want to see the result for First_Values and I matched it for the case with "is" = 1

SQL Fiddle Demo

Answer 2

Use min() instead:

select min(case when a.is = 1 then a.date end) over (partition by a.id)