Re: DISTINCT in STRING_AGG

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" <pgsqladmin(at)geoff(dot)dj>
To: "Sterpu Victor" <victor(at)caido(dot)ro>
Cc: "PostgreSQL General" <pgsql-general(at)postgresql(dot)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 <victor(at)caido(dot)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​
>