title: get first or last item in an aggregate when doing GROUP BY
body: <p>I came across the following old discussion on Google Groups about the capability of selecting the first/last value in an aggregate:</p>
  
  <p><a href="https://groups.google.com/forum/?fromgroups=#!msg/bigquery-discuss/1WAJw1UC73w/_RbUCsMIvQ4J" rel="nofollow">https://groups.google.com/forum/?fromgroups=#!msg/bigquery-discuss/1WAJw1UC73w/_RbUCsMIvQ4J</a></p>
  
  <p>I was wondering if the answer given is still up-to-date. More specifically, is it possible, without doing JOIN or using nested records to do something like:<br>
  SELECT foo, LAST(bar) last_bar FROM table GROUP BY foo HAVING last_bar = b<br>
  that for the following table:  </p>
  
  <pre><code>foo, bar  
  1, a  
  1, b  
  2, b  
  2, c  
  3, b
  </code></pre>
  
  <p>would return:  </p>
  
  <pre><code>foo, last_bar  
  1, b  
  3, b 
  </code></pre>
  
  <p>If it is not possible, I was thinking about doing the same with a combination of </p>
  
  <p><code>GROUP_CONCAT</code> and <code>REGEXP_MATCH</code> on the end of the concatenation:  </p>
  
  <pre><code>SELECT foo, GROUP_CONCAT(bar) concat_bar from table GROUP BY foo HAVING REGEXP_MATCH(concat_bar, "b$")  
  </code></pre>
  
  <p>but that only works if aggregation is done in the order of the rows. Is it the case?</p>