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!

Answer 1

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