From: | Israel Brewster <israel(at)ravnalaska(dot)net> |
---|---|
To: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | sub-select with multiple records, columns |
Date: | 2017-06-19 20:17:32 |
Message-ID: | 34A99D9B-819D-404A-BABB-AF0CC29AF351@ravnalaska.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
I have two tables, a ticket table and a notes table, set up where each ticket can have multiple notes. I'm trying to come up with a query that returns the ticket fields as well as a field that is an array type field with the values being json-encoded note records. I've come up with the following subselect query, which works:
SELECT
...
(SELECT
array_agg(to_json(row(notedate,username,note)))
FROM sabrenotes
INNER JOIN users ON author=users.id
WHERE ticket=sabretickets.id ) notes
FROM tickets
WHERE ...
The only problem with this query is that the notes aren't sorted. Of course, simply adding an ORDER BY clause to the sub-select doesn't work - it throws an error about needing to use notedate in a GROUP BY clause or aggregate function. Is there some way I can get sorting as well here? Of course, I could just run a second query to get the notes, and combine in code, but that's no fun... :-)
-----------------------------------------------
Israel Brewster
Systems Analyst II
Ravn Alaska
5245 Airport Industrial Rd
Fairbanks, AK 99709
(907) 450-7293
-----------------------------------------------
From | Date | Subject | |
---|---|---|---|
Next Message | Thomas Kellerer | 2017-06-19 20:29:12 | Re: sub-select with multiple records, columns |
Previous Message | Dmitry O Litvintsev | 2017-06-19 19:53:55 | Re: autovacuum holds exclusive lock on table preventing it from to be updated |