Menu
I am currently using BigQuery and GROUP_CONCAT which works perfectly fine. However, when I try to add a ORDER BY clause to the GROUP_CONCAT statement like I would do in SQL, I receive an error.
So e.g., something like
SELECT a, GROUP_CONCAT(b ORDER BY c)
FROM test
GROUP BY a
The same happens if I try to specify the separator.
Any ideas on how to approach this?
732
Instead, use the handy preview feature in BigQuery. Just click on the table name, and then click on the Preview tab to see the top 100 rows.
ARRAY_AGG. Returns an ARRAY of expression values. To learn more about the optional arguments in this function and how to use them, see Aggregate function calls.
To convert an ARRAY into a set of rows, also known as "flattening," use the UNNEST operator. UNNEST takes an ARRAY and returns a table with a single row for each element in the ARRAY . Because UNNEST destroys the order of the ARRAY elements, you may wish to restore order to the table.
Standard SQL mode in BigQuery does support ORDER BY clause within some aggregate functions, including STRING_AGG, for example:
#standardSQL
select string_agg(t.x order by t.y)
from unnest([struct<x STRING, y INT64>('a', 5), ('b', 1), ('c', 10)]) t
will result in
b,a,c
Documentation is here: https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators#using-order-by-with-aggregate-functions
124
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
