On Fri, Sep 1, 2017 at 6:22 AM, Charles Clavadetscher <clavadetsc...@swisspug.org> wrote: > Hello > >> -----Original Message----- >> From: pgsql-general-ow...@postgresql.org >> [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Bob Jones >> Sent: Freitag, 1. September 2017 10:12 >> To: pgsql-general <pgsql-general@postgresql.org> >> Subject: [GENERAL] Issue with json_agg() and ordering >> >> Hi, >> >> >> Could anyone give me a few pointers as to how I might resolve the following : >> >> select json_agg(my_table) from (my_table) where foo='test' and bar='f' >> order by last_name asc, first_name asc; >> >> ERROR: column "my_table.last_name" must appear in the GROUP BY clause or be >> used in an aggregate function LINE 1: >> ...foo='f' order by last_name ... > > I guess that the order by should be in the aggregation. > > SELECT json_agg(a.* ORDER BY a.last_name, a.last_year DESC) > FROM my_table a;
yes. however, you would say, json_agg(a... not 'a.*'). The .* notation only works in certain contexts, and is transformed at parse time to, a.col1, a.col2, a.col3... which would not work inside an aggregation function which can only handle a single column or record. merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
