title: Select value based on two parameters : another column string value and max(date) with BigQuery
body: <p>I have the following table :</p>
  
  <pre><code>AccountID   Email              status_update  value   date (dd/mm/yyyy) 
  123456      foo@gmail.com      state1          19     02/02/2016
  123456      foo@gmail.com      state2          20     10/10/2014
  123456      foo@gmail.com      state2          35     15/10/2015
  456123      bar@gmail.com      state2          45     05/04/2017
  789123      foobar@gmail.com   state2          10     22/04/2016
  789123      foobar@gmail.com   state1          22     17/06/2018
  456345      cool@gmail.com     state1          10     13/08/2017
  456345      cool@gmail.com     state2          05     09/07/2015
  456345      cool@gmail.com     state2          17     09/07/2014
  </code></pre>
  
  <p>How do I return the earliest value when <code>status_update = state2</code>?</p>
  
  <p>So for example looking at my table for <code>foo@gmail.com</code> I would like to get <code>20</code>. Which is the earliest value (<code>10/10/2014</code>) of <code>state2</code> for this account.</p>
  
  <p>As you might guess, I can't use a simple <code>where status_update = state2</code> statement here since this is part of a more global query where the final output will be grouped by AccountID and Email. </p>
  
  <p>Basically what I would like to be able to do this  </p>
  
  <p><code>ARRAY_AGG(value WHERE status_update = state2 ORDER BY date ASC LIMIT 1)[OFFSET (0)] as Account_Status</code></p>
  
  <p>or something like this :</p>
  
  <p><code>(value when MIN(CASE WHEN status_update = state2 THEN created END)) as EarliestValue</code></p>
  
  <p>Hopefully my question is clear. Thanks.</p>