Re: DISTINCT in STRING_AGG

["Sterpu Victor" <victor@xxxxxxxx>]

I can't skip the ordering.

I'm sure aqjs3 is the one that produces the duplication.

I guess subqueries are the only option, like this:

SELECT atjs.id,  tmp.children AS children
FROM administration.ad_query_join_select atjs

 

JOIN (SELECT
 atjs.id, STRING_AGG(CAST(aqjs1.id AS VARCHAR), ',' ORDER BY aqjs1.to_left) AS children
 FROM administration.ad_query_join_select atjs
 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)
 WHERE aqjs2.id IS NULL AND atjs.id_ad_query = 475543
 GROUP BY atjs.id) tmp ON (tmp.id = atjs.id)
 
JOIN administration.ad_query aq ON (aq.id=atjs.id_ad_query)
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 atjs.id_ad_query = 475543
GROUP BY aq.id, atjs.id, tmp.children
ORDER BY aq.id ASC, atjs.to_left ASC;

 

 

Result is:

id          ; children

1399029;"1399031"
1399031;"1399032,1399033"

Is there a better way? I usualy try to avoid subqueries.

 

------ Original Message ------

From: "Geoff Winkless" <pgsqladmin@xxxxxxxx>

To: "Sterpu Victor" <victor@xxxxxxxx>

Cc: "PostgreSQL General" <pgsql-general@xxxxxxxxxxxxxx>

Sent: 11/29/2015 6:42:18 PM

Subject: Re: DISTINCT in STRING_AGG

 

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​

DISCLAIMER:
Acest mesaj de posta electronica si documentele aferente sunt confidentiale. Este interzisa distribuirea, dezvaluirea sau orice alt mod de utilizare a lor. Daca nu sunteti destinatarul acestui mesaj, este interzis sa actionati in baza acestor informatii. Citirea, copierea, distribuirea, dezvaluirea sau utilizarea in alt mod a informatiei continute in acest mesaj constituie o incalcare a legii. Daca ati primit mesajul din greseala, va rugam sa il distrugeti, anuntand expeditorul de eroarea comisa. Intrucat nu poate fi garantat faptul ca posta electronica este un mod sigur si lipsit de erori de transmitere a informatiilor, este responsabilitatea dvs. sa va asigurati ca mesajul (inclusiv documentele alaturate lui) este validat si autorizat spre a fi utilizat in mediul dvs.