On 28 November 2015 at 18:35, Sterpu Victor <[email protected]> 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
