public inbox for [email protected]
help / color / mirror / Atom feedORDER BY elements in ARRAY
6+ messages / 3 participants
[nested] [flat]
* ORDER BY elements in ARRAY
@ 2024-08-27 11:22 Andreas Joseph Krogh <[email protected]>
0 siblings, 2 replies; 6+ messages in thread
From: Andreas Joseph Krogh @ 2024-08-27 11:22 UTC (permalink / raw)
To: [email protected]
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 = 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 “firstname and lastname of first element, the same for
second etc.”
Any way to do this?
--
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
[email protected] <mailto:[email protected]>
www.visena.com <https://www.visena.com;
<https://www.visena.com;
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: ORDER BY elements in ARRAY
@ 2024-08-27 11:44 Guillaume Lelarge <[email protected]>
parent: Andreas Joseph Krogh <[email protected]>
1 sibling, 0 replies; 6+ messages in thread
From: Guillaume Lelarge @ 2024-08-27 11:44 UTC (permalink / raw)
To: Andreas Joseph Krogh <[email protected]>; +Cc: [email protected]
Hi,
Le mar. 27 août 2024 à 13:23, Andreas Joseph Krogh <[email protected]> a
écrit :
> 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 = 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 “firstname and lastname of first element,
> the same for second etc.”
>
>
>
> Any way to do this?
>
> array_agg might be what you're looking for, but you'll have to extract the
subquery, and transform it to a join on the main query.
Regards.
--
Guillaume.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: ORDER BY elements in ARRAY
@ 2024-08-27 12:39 David G. Johnston <[email protected]>
parent: Andreas Joseph Krogh <[email protected]>
1 sibling, 1 reply; 6+ messages in thread
From: David G. Johnston @ 2024-08-27 12:39 UTC (permalink / raw)
To: Andreas Joseph Krogh <[email protected]>; +Cc: [email protected] <[email protected]>
On Tuesday, August 27, 2024, Andreas Joseph Krogh <[email protected]>
wrote:
> 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 = comp.entity_id
>
>
> Add order by here
>
>
> ) as person_array
>
> FROM onp_crm_relation comp
> ORDER BY person_array
> ;
>
>
David J.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: ORDER BY elements in ARRAY
@ 2024-08-27 14:59 Andreas Joseph Krogh <[email protected]>
parent: David G. Johnston <[email protected]>
0 siblings, 1 reply; 6+ messages in thread
From: Andreas Joseph Krogh @ 2024-08-27 14:59 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; +Cc: [email protected] <[email protected]>
På tirsdag 27. august 2024 kl. 14:39:34, skrev David G. Johnston <
[email protected] <mailto:[email protected]>>:
[…]
Add order by here ) as person_array FROM onp_crm_relation comp ORDER BY
person_array ;
David J.
This doesn't really do it. I'm looking for something analogous to Java's
Comparator-interface so the custom datatype knows how to sort itself.
--
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
[email protected] <mailto:[email protected]>
www.visena.com <https://www.visena.com;
<https://www.visena.com;
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: ORDER BY elements in ARRAY
@ 2024-08-27 16:03 David G. Johnston <[email protected]>
parent: Andreas Joseph Krogh <[email protected]>
0 siblings, 1 reply; 6+ messages in thread
From: David G. Johnston @ 2024-08-27 16:03 UTC (permalink / raw)
To: Andreas Joseph Krogh <[email protected]>; +Cc: [email protected] <[email protected]>
On Tuesday, August 27, 2024, Andreas Joseph Krogh <[email protected]>
wrote:
> På tirsdag 27. august 2024 kl. 14:39:34, skrev David G. Johnston <
> [email protected]>:
>
> […]
> Add order by here
>
>> ) as person_array
>>
>> FROM onp_crm_relation comp
>> ORDER BY person_array
>> ;
>>
>>
> David J.
>
>
>
> This doesn't really do it. I'm looking for something analogous to Java's
> Comparator-interface so the custom datatype knows how to sort itself.
>
Ah. Then either just put those two values into the first two field
positions of your custom type and live with an easy, but also
self-contained, hack, or figure out what incantations of create operator
and/or create operator family are need to install custom behavior for the
b-tree < and > operators. I’m 80% sure it’s doable, 60% in plpgsql…
David J.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: ORDER BY elements in ARRAY
@ 2024-08-27 16:27 Andreas Joseph Krogh <[email protected]>
parent: David G. Johnston <[email protected]>
0 siblings, 0 replies; 6+ messages in thread
From: Andreas Joseph Krogh @ 2024-08-27 16:27 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; +Cc: [email protected] <[email protected]>
På tirsdag 27. august 2024 kl. 18:03:37, skrev David G. Johnston <
[email protected] <mailto:[email protected]>>:
On Tuesday, August 27, 2024, Andreas Joseph Krogh <[email protected]
<mailto:[email protected]>> wrote:
[…]
This doesn't really do it. I'm looking for something analogous to Java's
Comparator-interface so the custom datatype knows how to sort itself.
Ah. Then either just put those two values into the first two field positions
of your custom type and live with an easy, but also self-contained, hack, or
figure out what incantations of create operator and/or create operator family
are need to install custom behavior for the b-tree < and > operators. I’m 80%
sure it’s doable, 60% in plpgsql…
David J.
Ah, seems CREATE OPERATOR is worth pursuing.
Thanks.
--
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
[email protected] <mailto:[email protected]>
www.visena.com <https://www.visena.com;
<https://www.visena.com;
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2024-08-27 16:27 UTC | newest]
Thread overview: 6+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-08-27 11:22 ORDER BY elements in ARRAY Andreas Joseph Krogh <[email protected]>
2024-08-27 11:44 ` Guillaume Lelarge <[email protected]>
2024-08-27 12:39 ` David G. Johnston <[email protected]>
2024-08-27 14:59 ` Andreas Joseph Krogh <[email protected]>
2024-08-27 16:03 ` David G. Johnston <[email protected]>
2024-08-27 16:27 ` Andreas Joseph Krogh <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox