På torsdag 11. januar 2018 kl. 12:00:55, skrev Andreas Joseph Krogh < andr...@visena.com <mailto:andr...@visena.com>>: Hi all. I have this custom-type: CREATE TYPE PERSONTYPE AS ( id BIGINT, firstname VARCHAR, lastname VARCHAR, initialsVARCHAR ); I'm returning a column with this type using the following query, which lists activities and its responsible-person (using the PERSONTYPE): SELECT act.entity_id AS project_id, act.name AS project , (SELECT ROW (p.entity_id, p.firstname, p.lastname, p.initials)::persontypeFROM onp_crm_person pWHERE act.responsible = p.onp_user_id) AS responsible_person fromonp_crm_activity act ORDER BY responsible_person ASC ; But I need to ORDER BY different properties of PERSONTYPE, ie. initials, and am wondering if there exists a mechanism to achieve the following: SELECT act.entity_id AS project_id, act.name AS project , (SELECT ROW (p.entity_id, p.firstname, p.lastname, p.initials)::persontypeFROM onp_crm_person pWHERE act.responsible = p.onp_user_id) AS responsible_person fromonp_crm_activity act ORDER BY responsible_person.initials ASC ; How do I do that? Thanks. I see that if I wrap the query in an outer query I can do it like this: SELECT * FROM ( SELECT act.entity_id AS project_id, act.name AS project , ( SELECT ROW(p.entity_id, p.firstname, p.lastname, p.initials)::persontype FROM onp_crm_person pWHERE act.responsible = p.onp_user_id) AS responsible_person fromonp_crm_activity act ) AS q ORDER BY (q.responsible_person).initials ASC ; Without the outer query: SELECT act.entity_id AS project_id, act.name AS project , (SELECT ROW (p.entity_id, p.firstname, p.lastname, p.initials)::persontypeFROM onp_crm_person pWHERE act.responsible = p.onp_user_id) AS responsible_person fromonp_crm_activity act ORDER BY (responsible_person).initials ; It fails with: ERROR: column "responsible_person" does not exist LINE 6: ORDER BY (responsible_person).initials
Is the only solution wrapping with an outer query? -- Andreas Joseph Krogh CTO / Partner - Visena AS Mobile: +47 909 56 963 andr...@visena.com <mailto:andr...@visena.com> www.visena.com <https://www.visena.com> <https://www.visena.com>
