Postgres Pro
Downloads
Home   >   mailing lists

Thread: ARRAY_AGG and ORDER

ARRAY_AGG and ORDER

From
"Sterpu Victor"
Date:
Hello
 
I need to order an array using another column in table ad_query_join_select.
I need something like this but this is not a valid SQL:
SELECT array_to_string(array_agg(aqjs.id ORDER BY aqjs.to_left), ',') AS str, aq.name
FROM ad_query aq
JOIN ad_query_join_select aqjs ON (aqjs.id_ad_query = aq.id)
GROUP BY aq.id, aq.name;
 
Is there some equivalent syntax in Postgres?
 
Thank you.

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.

Re: ARRAY_AGG and ORDER

From
Geoff Winkless
Date:
On 26 November 2015 at 12:43, Sterpu Victor <victor@caido.ro> wrote:
Hello
 
I need to order an array using another column in table ad_query_join_select.
I need something like this but this is not a valid SQL:
SELECT array_to_string(array_agg(aqjs.id ORDER BY aqjs.to_left), ',') AS str, aq.name
FROM ad_query aq
JOIN ad_query_join_select aqjs ON (aqjs.id_ad_query = aq.id)
GROUP BY aq.id, aq.name;
 
Is there some equivalent syntax in Postgres?
 
​As per the manual?

​http://www.postgresql.org/docs/current/static/sql-expressions.html#SYNTAX-AGGREGATES

​SELECT string_agg(a, ',' ORDER BY a) FROM table;

Re: ARRAY_AGG and ORDER

From
"Sterpu Victor"
Date:
Yes, thank you. :)
 
------ Original Message ------
From: "Sterpu Victor" <victor@caido.ro>
To: "Geoff Winkless" <pgsqladmin@geoff.dj>
Sent: 11/26/2015 2:51:48 PM
Subject: Re[2]: [GENERAL] ARRAY_AGG and ORDER
 
Yes, thank you. :)
 
 
------ Original Message ------
From: "Geoff Winkless" <pgsqladmin@geoff.dj>
To: "Sterpu Victor" <victor@caido.ro>
Cc: "PostgreSQL General" <pgsql-general@postgresql.org>
Sent: 11/26/2015 2:46:33 PM
Subject: Re: [GENERAL] ARRAY_AGG and ORDER
 
On 26 November 2015 at 12:43, Sterpu Victor <victor@caido.ro> wrote:
Hello
 
I need to order an array using another column in table ad_query_join_select.
I need something like this but this is not a valid SQL:
SELECT array_to_string(array_agg(aqjs.id ORDER BY aqjs.to_left), ',') AS str, aq.name
FROM ad_query aq
JOIN ad_query_join_select aqjs ON (aqjs.id_ad_query = aq.id)
GROUP BY aq.id, aq.name;
 
Is there some equivalent syntax in Postgres?
 
​As per the manual?

​http://www.postgresql.org/docs/current/static/sql-expressions.html#SYNTAX-AGGREGATES

​SELECT string_agg(a, ',' ORDER BY a) FROM table;

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.