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
– Mikhail Berlyant, Commented Aug 10, 2020 at 17:19

Matteo Felici · Accepted Answer · 2020-08-10 14:23:58Z

2

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

answered Aug 10, 2020 at 14:23

Matteo Felici

1,10711 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Finding Error when running STRING_AGG function

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related