MIME-Version: 1.0
References: 
 <VisenaEmail.11.efc8bf0cbc6c75fa.1919390a6d7@origo-test01.app.internal.visena.net>
In-Reply-To: 
 <VisenaEmail.11.efc8bf0cbc6c75fa.1919390a6d7@origo-test01.app.internal.visena.net>
From: Guillaume Lelarge <guillaume@lelarge.info>
Date: Tue, 27 Aug 2024 13:44:02 +0200
Message-ID: 
 <CAECtzeWmD1KR088eRu-_daWn=NLJ3GGXpa=3-hfqNHogM0TZiw@mail.gmail.com>
Subject: Re: ORDER BY elements in ARRAY
To: Andreas Joseph Krogh <andreas@visena.com>
Cc: pgsql-sql@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000c0bca70620a8c202"
Archived-At: 
 <https://www.postgresql.org/message-id/CAECtzeWmD1KR088eRu-_daWn%3DNLJ3GGXpa%3D3-hfqNHogM0TZiw%40mail.gmail.com>
Precedence: bulk

--000000000000c0bca70620a8c202
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

Hi,

Le mar. 27 ao=C3=BBt 2024 =C3=A0 13:23, Andreas Joseph Krogh <andreas@visen=
a.com> a
=C3=A9crit :

> Hi, I have this query:
>
>
>
> SELECT
>     comp.entity_id as company_id, comp.companyname AS company_name
>      , ARRAY(
>         SELECT
>             (pers.entity_id
>                 , pers.firstname
>                 , pers.lastname
>                 )::PersonTypeTest
>         FROM onp_crm_person AS pers
>         WHERE pers.relation_id =3D comp.entity_id
>        ) as person_array
>
> FROM onp_crm_relation comp
> ORDER BY person_array
> ;
>
>
>
> And I'd like to ORDER BY (conceptually) lower(pers.firstname),
> lower(pers.lastname). I realize there might be more than 1 person in the
> array, so I'd like to order by =E2=80=9Cfirstname and lastname of first e=
lement,
> the same for second etc.=E2=80=9D
>
>
>
> Any way to do this?
>
> array_agg might be what you're looking for, but you'll have to extract th=
e
subquery, and transform it to a join on the main query.

Regards.


--=20
Guillaume.

--000000000000c0bca70620a8c202
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr">Hi,<br></div><br><div class=3D"gmail_quot=
e"><div dir=3D"ltr" class=3D"gmail_attr">Le=C2=A0mar. 27 ao=C3=BBt 2024 =C3=
=A0=C2=A013:23, Andreas Joseph Krogh &lt;<a href=3D"mailto:andreas@visena.c=
om">andreas@visena.com</a>&gt; a =C3=A9crit=C2=A0:<br></div><blockquote cla=
ss=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid =
rgb(204,204,204);padding-left:1ex"><div><p>Hi, I have this query:</p><p>=C2=
=A0</p><pre><code>SELECT
    comp.entity_id as company_id, comp.companyname AS company_name
     , ARRAY(
        SELECT
            (pers.entity_id
                , pers.firstname
                , pers.lastname
                )::PersonTypeTest
        FROM onp_crm_person AS pers
        WHERE pers.relation_id =3D comp.entity_id
       ) as person_array

FROM onp_crm_relation comp
ORDER BY person_array
;</code></pre><p>=C2=A0</p><p>And I&#39;d like to ORDER BY (conceptually) l=
ower(pers.firstname), lower(pers.lastname). I realize there might be more t=
han 1 person in the array, so I&#39;d like to order by =E2=80=9Cfirstname a=
nd lastname of first element, the same for second etc.=E2=80=9D</p><p>=C2=
=A0</p><p>Any way to do this?</p><p></p></div></blockquote><div>array_agg m=
ight be what you&#39;re looking for, but you&#39;ll have to extract the sub=
query, and transform it to a join on the main query.</div><div><br></div></=
div><div>Regards.</div><div><br></div><div><br></div><div><span class=3D"gm=
ail_signature_prefix">-- </span><br><div dir=3D"ltr" class=3D"gmail_signatu=
re"><div dir=3D"ltr"><div><div dir=3D"ltr"><div>Guillaume.<br></div></div><=
/div></div></div></div></div>

--000000000000c0bca70620a8c202--