title: How to get the first non-NULL value in SQL?
body: <p>I have three columns of data. One column has ID and the second column has a date and the third column has BMI value. I want to create a fourth column that has the first_value based on date(ascending order) from the third column which is not null or avoiding the null.</p>
  
  <p>So far, I have tried first_value in plain form which didn't work. I tried subsetting first_value inside the case when statement as </p>
  
  <pre><code>CASE 
    WHEN BMI IS NOT NULL THEN (FIRST_VALUE(BMI) OVER PARTITION BY PID ORDER BY DATE)) 
    ELSE 0 
  END AS FIRSTNOTNULLVALUE_BMI
  </code></pre>
  
  <p>gave me 0s. </p>
  
  <pre><code>id    date         BMI 
  1   2000-01-01    NULL
  1   2003-05-01    18.1
  1   2002-07-15    25.8
  2   2009-09-25    NULL
  2   2015-04-18    23.5
  </code></pre>
  
  <p>Any suggestions??</p>