Snowflake: ORDER BY clause in ARRAY_AGG

Question

Requirement: order by clause within array_agg() function in Snowflake

create table test("CompanyID","GroupIDs","GroupNames")as select*from(values(1111 ,1 ,'ABC'), (1111 ,5 ,'PRS'));

Expected output

CompanyID	GroupIDs	GroupNames
1111	[1, 5]	[ABC, PRS]

Here in above example, it's in ascending order and GroupID=1 corresponds to GroupName=ABC and so on.

Below sql doesn't order by groupid in the final result

SELECT companyid, ARRAY_AGG(groupid) AS groupid, ARRAY_AGG(groupname) AS groupname FROM ( SELECT cgd.companyid ,cg.groupid,cgd.companyname,cg.groupname FROM gd LEFT JOIN cg ON cg.groupid = cgd.groupid ORDER BY cgd.companyid , cg.groupid, cg.groupname ) t GROUP BY ALL;

Above query produces results as below

CompanyID	GroupIDs	GroupNames
1111	[5,1]	[PRS, ABC]

Zegarek · Accepted Answer · 2025-02-25 16:41:56Z

select "CompanyID" , array_agg("GroupIDs")within group(order by "GroupIDs") as "GroupIDs" , array_agg("GroupNames")within group(order by "GroupIDs") as "GroupNames" from test group by "CompanyID" order by "CompanyID";

# CompanyID	[] GroupIDs	[] GroupNames
1111	{1,5}	{ABC,PRS}

Quoting the doc:

ARRAY_AGG( [ DISTINCT ] <expr1> ) [ WITHIN GROUP ( <orderby_clause> ) ]

WITHIN GROUP orderby_clause
Clause that contains one or more expressions (typically column names) that determine the order of the values in each array.

The WITHIN GROUP(ORDER BY) syntax supports the same parameters as the main ORDER BY clause in a SELECT statement. See ORDER BY.

I tested the above on Snowflake, but here's also an equivalent PostgreSQL demo at db<>fiddle.

Moseleyi · Accepted Answer · 2025-02-25 16:15:40Z

You can use ORDER BY in the WITHIN GROUP clause for this function:

ARRAY_AGG(groupid) WITHIN GROUP(ORDER BY groupid)

https://docs.snowflake.com/en/sql-reference/functions/array_agg

Collectives™ on Stack Overflow

Snowflake: ORDER BY clause in ARRAY_AGG

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related