CODEWITHSUNDEEP
google-bigquery
samuelbrody1249
samuelbrody1249

Reputation: 4767

Aggregating on max date in bigquery

I have the following three rows of data in BQ:

date        instance_id   value
2020-01-01  2            'hello'
2020-01-04  2             NULL
2020-02-03  3            'new'

How would I group to get the 'newest' value by date? I tried doing a join on the following:

SELECT * from `historical_prices` m1 join `historical_prices` m2 
ON (m1.instance_id=m2.instance_id and m1.date=max(m2.date))
WHERE date > '2020-01-01'

But got the following error:

Aggregate function MAX not allowed in JOIN ON clause at [7:100]

What would be the proper pattern to use to get the above?

Upvotes: 0

Views: 956

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172944

Below is for BigQuery Standard SQL 

#standardSQL
SELECT AS VALUE ARRAY_AGG(t ORDER BY date DESC LIMIT 1)[OFFSET(0)]
FROM `project.dataset.historical_prices` t
WHERE date > '2020-01-01'
GROUP BY instance_id

Upvotes: 1

Related Questions