| 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: | Whole Thread | Raw Message | 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 |