Search code examples
sqloraclewindow-functions

Window Function with a condition

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
Solution

  • Use min() instead:

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