| From: | "Sterpu Victor" <victor(at)caido(dot)ro> | 
|---|---|
| To: | "PostgreSQL General" <pgsql-general(at)postgresql(dot)org> | 
| Subject: | DISTINCT in STRING_AGG | 
| Date: | 2015-11-28 18:35:18 | 
| Message-ID: | em769050b5-90d1-4004-ace8-5365f9b3ff4d@victor-pc | 
| Views: | Whole Thread | Raw Message | Download mbox | Resend email | 
| Thread: | |
| Lists: | pgsql-general | 
Hello
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?
Thank you.
| From | Date | Subject | |
|---|---|---|---|
| Next Message | John J. Turner | 2015-11-28 20:00:29 | Re: DISTINCT in STRING_AGG | 
| Previous Message | Adrian Klaver | 2015-11-28 02:39:12 | Re: Problems with pg_upgrade after change of unix user running db. |