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?

score 12 · Answer 1 · answered Jan 18 '20 at 00:33

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)

score 2 · Answer 2 · edited May 08 '14 at 12:07

2

I was trying to solve a similar problem and came to the same conclusion using GROUP_CONCAT

Give this a try:

SELECT foo, REGEXP_REPLACE(group_concat(bar),".*,","") as last_bar 
FROM [dataset.table] 
GROUP BY foo

edited May 08 '14 at 12:07

Rohit

3,087
3
20
29

answered Oct 10 '12 at 19:31

David M Smith

2,212
4
21
27

This is similar indeed to what is proposed above. That being said, it also only works if the aggregation is done "in order". Is it the case? – Thomas Gerber Oct 11 '12 at 00:53
That has been my experience thus far with the tests I've run, but I'm not certain. – David M Smith Oct 11 '12 at 20:21

score -2 · Answer 3 · answered Oct 18 '12 at 23:14

-2

There is no guarantee to the ordering of records stored in BigQuery, so this would likely fail at some point. Will the "last entry" always be the largest? If so, perhaps the following is what you're looking for?

SELECT foo, MAX(bar) FROM test GROUP BY foo

answered Oct 18 '12 at 23:14

Jim Caputo

85
2

4

How to get to France: There's no guarantee that turning left out of your driveway will get you to France but if it does, perhaps try turning left out of your driveway? – Adam Marples Aug 15 '18 at 16:17

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

3 Answers3

Linked