Finding Error when running STRING_AGG function

Question

I would like to ask about a script in BigQuery. So, I tried to use a query below

SELECT id, STRING_AGG(DISTINCT status, ', ' ORDER BY timestamp) AS grouping
FROM table
GROUP BY id

But I couldn't run it since it gave me an error

An aggregate function that has both DISTINCT and ORDER BY arguments can only ORDER BY expressions that are arguments to the function

Could anyone help me to fix the error? Thank you in advance!

When you order Distinct values of `status` - which `timestamp` of multiple rows with that status you want to use? this is why it is not supported in `STRING_AGG` as is because it just would not know how to order. So you need to clarify your requirements/expectations for us to help you — Mikhail Berlyant, Aug 10 '20 at 17:19

score 2 · Accepted Answer · answered Aug 10 '20 at 14:23

Do you want the distinct statuses ordered by timestamp? If so, you can first order for each id the column status by timestamp, then aggregate.

WITH ordered as (
    SELECT id, status
    FROM table
    ORDER BY id, row_number() over (partition by id ORDER BY timestamp)
)
SELECT id, STRING_AGG(DISTINCT status, ', ') AS grouping
FROM ordered
GROUP BY id

Finding Error when running STRING_AGG function

1 Answers1