From: | "John J(dot) Turner" <fenwayriffs(at)gmail(dot)com> |
---|---|
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-28 20:00:29 |
Message-ID: | 0CE5D95E-C45E-4CA2-ADC6-F419DBB47C5E@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Nov 28, 2015, at 1:35 PM, Sterpu Victor <victor(at)caido(dot)ro> wrote:
> 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?
Just to mention, this looks like a good candidate for range types and CTE’s.
The > / < comparisons appear to be mutually exclusive in each LEFT JOIN clause, so it’s not apparent why aqjs3 is causing duplication, as you’ve stated.
As far as I can see, without providing us with your table constraints/keys, there’s no way to determine what makes your ID values unique…
However, if you defer your STRING_AGG until after you derive a distinct “staging” result set from the joins, then you can effect uniqueness -
e.g. (air code):
WITH q AS
(SELECT aq.id aq_parent_id, atjs.id atjs_parent_id, CAST(aqjs1.id AS VARCHAR) child FROM ... GROUP BY aq.id, atjs.id, aqjs1.id)
SELECT atjs_parent_id, STRING_AGG(child,’,’ ORDER BY aqjs.to_left) children
FROM q LEFT JOIN (SELECT DISTINCT id_ad_query, to_left FROM administration.ad_query_join_select) aqjs ON …
GROUP BY aq_parent_id, parent
ORDER BY aq_parent_id, atjs.to_left;
Something along these lines ‘may’ produce a unique set of child values for each id by which to perform a STRING_AGG on, but again, I can only guess based on the lack of definition provided for your table constraints.
John
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Smith | 2015-11-29 02:27:51 | JSONB performance enhancement for 9.6 |
Previous Message | Sterpu Victor | 2015-11-28 18:35:18 | DISTINCT in STRING_AGG |