Alternative to ROW_NUMBER

QueryStash

Thanks to this presentation from Google for this great tip.

Avoids resource exceeded issue on BigQuery with very large data, much better approach.

1SELECT
2  item,
3  ARRAY_AGG(price ORDER BY date DESC)[OFFSET(0)] AS latest_price
4FROM products
5GROUP BY item

SELECT
  item,
  ARRAY_AGG(price ORDER BY date DESC)[OFFSET(0)] AS latest_price
FROM products
GROUP BY item

Public

QueryStash

Created: Sep 27th 2020

QueryStash

Made with ❤️ by @Yekalb

Resources

Examples

Stay up to date