Re: DISTINCT in STRING_AGG

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:
>SELECT atjs.id, tmp.children AS children
>FROM administration.ad_query_join_select atjs
>
>JOIN (SELECT
> atjs.id, STRING_AGG(CAST(aqjs1.id AS VARCHAR), ',' ORDER BY
>aqjs1.to_left) AS children
> FROM administration.ad_query_join_select atjs
> LEFT JOIN administration.ad_query_join_select aqjs1 ON
>(aqjs1.id_ad_query = atjs.id_ad_query AND aqjs1.to_left>atjs.to_left
>AND aqjs1.to_right<atjs.to_right)
> LEFT JOIN administration.ad_query_join_select aqjs2 ON
>(aqjs2.id_ad_query = atjs.id_ad_query AND aqjs2.to_left>atjs.to_left
>AND aqjs2.to_right<atjs.to_right AND aqjs2.to_left<aqjs1.to_left AND
>aqjs2.to_right>aqjs1.to_right)
> WHERE aqjs2.id IS NULL AND atjs.id_ad_query = 475543
> GROUP BY atjs.id) tmp ON (tmp.id = atjs.id)
>
>JOIN administration.ad_query aq ON (aq.id=atjs.id_ad_query)
>LEFT JOIN administration.ad_query_join_select aqjs3 ON
>(aqjs3.id_ad_query = atjs.id_ad_query AND aqjs3.to_left<atjs.to_left
>AND aqjs3.to_right>atjs.to_right)
>WHERE atjs.id_ad_query = 475543
>GROUP BY aq.id, atjs.id, tmp.children
>ORDER BY aq.id ASC, atjs.to_left ASC;
>
>
Result is:
id ; children
1399029;"1399031"
1399031;"1399032,1399033"
Is there a better way? I usualy try to avoid subqueries.

------ 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 6:42:18 PM
Subject: Re: [GENERAL] DISTINCT in STRING_AGG

>On 28 November 2015 at 18:35, Sterpu Victor <victor(at)caido(dot)ro> wrote:
>>Can I make a distinct STRING_AGG?
>>This is my query :
>>SELECT atjs.id, STRING_AGG(CAST(aqjs1.id AS VARCHAR), ',' ORDER BY
>>aqjs1.to_left) AS children
>>FROM administration.ad_query_join_select atjs
>>JOIN administration.ad_query aq ON (aq.id=atjs.id_ad_query)
>>LEFT JOIN administration.ad_query_join_select aqjs1 ON
>>(aqjs1.id_ad_query = atjs.id_ad_query AND aqjs1.to_left>atjs.to_left
>>AND aqjs1.to_right<atjs.to_right)
>>LEFT JOIN administration.ad_query_join_select aqjs2 ON
>>(aqjs2.id_ad_query = atjs.id_ad_query AND aqjs2.to_left>atjs.to_left
>>AND aqjs2.to_right<atjs.to_right AND aqjs2.to_left<aqjs1.to_left AND
>>aqjs2.to_right>aqjs1.to_right)
>>LEFT JOIN administration.ad_query_join_select aqjs3 ON
>>(aqjs3.id_ad_query = atjs.id_ad_query AND aqjs3.to_left<atjs.to_left
>>AND aqjs3.to_right>atjs.to_right)
>>WHERE aqjs2.id IS NULL AND atjs.id_ad_query = 475543
>>GROUP BY aq.id, atjs.id
>>ORDER BY aq.id ASC, atjs.to_left ASC;
>>
>>And "childen" contain doubles. The result is:
>>id ; children
>>1399029;"1399031,1399031"
>>1399031;"1399032,1399032,1399032,1399033,1399033,1399033"
>>
>>There are doubles because of the join aqjs3 witch is producing this
>>problem.
>>Can I make it so the children ID's are unique?
>>
>
>Well if you can live with losing the to_left ordering, then you could
>just do
>
>SELECT STRING_AGG(DISTINCT CAST(aqjs1.id​ AS VARCHAR), '') AS children
>​
>​ ...​
>
>​no?
>
>Geoff​
>