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> |
Subject: | Re: DISTINCT in STRING_AGG |
Date: | 2015-11-29 16:42:18 |
Message-ID: | CAEzk6ffA+AWoYPXDMnwH5+OAdojbRwA8XzyjCJyyBdgwyieBfg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | George Neuner | 2015-11-29 17:31:19 | Re: using a postgres table as a multi-writer multi-updater queue |
Previous Message | Arthur Silva | 2015-11-29 15:30:45 | Re: JSONB performance enhancement for 9.6 |