Search Postgresql Archives

Re: DISTINCT in STRING_AGG

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On 28 November 2015 at 18:35, Sterpu Victor <victor@xxxxxxxx> 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​


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux