| 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 ----------------------------------------------- |
BEGIN:VCARD VERSION:3.0 N:Brewster;Israel;;; FN:Israel Brewster ORG:Frontier Flying Service;MIS TITLE:PC Support Tech II EMAIL;type=INTERNET;type=WORK;type=pref:isr...@frontierflying.com TEL;type=WORK;type=pref:907-450-7293 item1.ADR;type=WORK;type=pref:;;5245 Airport Industrial Wy;Fairbanks;AK;99701; item1.X-ABADR:us CATEGORIES:General X-ABUID:36305438-95EA-4410-91AB-45D16CABCDDC\:ABPerson END:VCARD
