Thank you.
I think there is no native function that will solve this problem,
intarray extension can't order as in the example query.
I could write a new SQL function but I solved the problem with
subqueries and works fine.
I need order because I use nested trees.
To_left and to_right are the coordinates for each node and the result
must be ordered by this to obtain the correct result.
------ Original Message ------
From: "Geoff Winkless" <pgsqlad...@geoff.dj>
To: "Sterpu Victor" <vic...@caido.ro>
Cc: "PostgreSQL General" <pgsql-general@postgresql.org>
Sent: 11/29/2015 10:51:32 PM
Subject: Re: Re[2]: [GENERAL] DISTINCT in STRING_AGG
On 29 November 2015 at 18:59, Sterpu Victor <vic...@caido.ro> wrote:
I can't skip the ordering.
I'm sure aqjs3 is the one that produces the duplication.
I guess subqueries are the only option, like this:
Well you could look at the intarray extension and a combination of
array_agg, uniq() and string_to_array:
http://www.postgresql.org/docs/current/static/intarray.html
but that's probably sledgehammer:nut time.
Not sure why you need to order the values you're getting back by
something other than the values themselves - is there a reason you
wouldn't want the "children" set to be ordered numerically? You can
still order the outer query by whatever you like, or you can order the
aggregate by the values themselves, it's just the DISTINCT inside the
aggregate query requires that an internal ORDER includes the ordering
term in the result.
Geoff
