get first or last item in an aggregate when doing GROUP BY

Question

I came across the following old discussion on Google Groups about the capability of selecting the first/last value in an aggregate:

https://groups.google.com/forum/?fromgroups=#!msg/bigquery-discuss/1WAJw1UC73w/_RbUCsMIvQ4J

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:
SELECT foo, LAST(bar) last_bar FROM table GROUP BY foo HAVING last_bar = b
that for the following table:

foo, bar  
1, a  
1, b  
2, b  
2, c  
3, b

would return:

foo, last_bar  
1, b  
3, b 

If it is not possible, I was thinking about doing the same with a combination of

GROUP_CONCAT and REGEXP_MATCH on the end of the concatenation:

SELECT foo, GROUP_CONCAT(bar) concat_bar from table GROUP BY foo HAVING REGEXP_MATCH(concat_bar, "b$")  

but that only works if aggregation is done in the order of the rows. Is it the case?

Answer 1

I like to use array aggregation to get first/last values:

SELECT foo, ARRAY_AGG(bar)[OFFSET(0)] AS bar FROM test GROUP BY foo;

You can also add LIMIT to aggregation: ARRAY_AGG(bar LIMIT 1) to make it faster.

It lets you use ORDER BY if you want to sort it by a column or get the last value instead: ARRAY_AGG(bar ORDER BY foo DESC)

Also you can filter out null values with ARRAY_AGG(bar IGNORE NULLS)