Sorting in array_agg function

Question

I have a query shown below

SELECT 
  league.country as id, 
  array_agg(DISTINCT row(league.id, league.name::varchar)) AS league_names, 

FROM league 
GROUP BY league.country

How can I sort by league.name::varchar in array_agg function?

Craig Ringer · Accepted Answer · 2014-03-09 02:53:33Z

13

Aggregates accept an ORDER BY. So you can write:

array_agg(thecol ORDER BY someothercol)

e.g.

array_agg(
  DISTINCT 
  row(league.id, league.name::varchar) 
  ORDER BY league.name
) AS league_names,

answered Mar 9, 2014 at 2:53

Craig Ringer

329k83 gold badges742 silver badges820 bronze badges

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

1 Answer 1