Le mar. 27 août 2024 à 13:23, Andreas Joseph Krogh <andreas@visena.com> 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.
