From jdmorgan@unca.edu Tue Feb 28 03:50:37 2012 Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125]) by mail.postgresql.org (Postfix) with ESMTP id E6ED69552CF for ; Mon, 27 Feb 2012 23:50:37 -0400 (AST) Received: from na3sys009aob122.obsmtp.com ([74.125.149.146]) by makus.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1S2E5D-00031g-9H for pgsql-sql@postgresql.org; Tue, 28 Feb 2012 03:50:36 +0000 Received: from mail-gx0-f178.google.com ([209.85.161.178]) (using TLSv1) by na3sys009aob122.postini.com ([74.125.148.12]) with SMTP ID DSNKT0xO8MEmEAuQ6HbT4zSrz1LzEW4+kkCR@postini.com; Mon, 27 Feb 2012 19:50:22 PST Received: by mail-gx0-f178.google.com with SMTP id i4so2652725ggm.23 for ; Mon, 27 Feb 2012 19:50:08 -0800 (PST) Received-SPF: pass (google.com: domain of jdmorgan@unca.edu designates 10.100.77.6 as permitted sender) client-ip=10.100.77.6; Authentication-Results: mr.google.com; spf=pass (google.com: domain of jdmorgan@unca.edu designates 10.100.77.6 as permitted sender) smtp.mail=jdmorgan@unca.edu Received: from mr.google.com ([10.100.77.6]) by 10.100.77.6 with SMTP id z6mr349001ana.77.1330401008754 (num_hops = 1); Mon, 27 Feb 2012 19:50:08 -0800 (PST) Received: by 10.100.77.6 with SMTP id z6mr236989ana.77.1330401008664; Mon, 27 Feb 2012 19:50:08 -0800 (PST) Received: from [192.168.1.103] (71-90-234-50.dhcp.gnvl.sc.charter.com. [71.90.234.50]) by mx.google.com with ESMTPS id v26sm2793752yhk.1.2012.02.27.19.50.07 (version=SSLv3 cipher=OTHER); Mon, 27 Feb 2012 19:50:08 -0800 (PST) Message-ID: <4F4C4EF4.4000006@gmail.com> Date: Mon, 27 Feb 2012 22:50:12 -0500 From: jdmorgan User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:7.0.1) Gecko/20110929 Thunderbird/7.0.1 MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: array_agg order by Content-Type: multipart/alternative; boundary="------------020106050109000408020101" X-Gm-Message-State: ALoCoQmftf82eD/G5XOZdmjMjiuD9LJJmcd7i/F44rgJmqZ90Yq139U5ZS9qmEioamNt9x+Bht66 X-Pg-Spam-Score: -4.2 (----) X-Archive-Number: 201202/87 X-Sequence-Number: 36380 This is a multi-part message in MIME format. --------------020106050109000408020101 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Hello, I am using a array_agg to get a subset of data to use in a query.However, I can't figure out how to get the data returned in the array_agg function to sort with an order by function.Any help would be appreciated.Here is my query as it is now.I am using postgresql 8: select pestproxshapes.pt_id, pestproxshapes.gid, array_to_string(array_agg(coalesce(pestproxdata.agent || ' | Closest Agent (km): ' || round(pestproxdata.metric1,2) || ' | Acres: ' || round(pestproxdata.metric2, 2) || ' | count: ' || pestproxdata.count || ' | yr: ' || pestproxdata.year || ' | prob: ' || round(pestproxdata.prob,2) || ' | host: ' || pestproxdata.host, 'no agents reported within 75km')), '\n') as AGENTS from pestproxshapes left outer join pestproxdata on pestproxshapes.pt_id=pestproxdata.pt_id where pestproxshapes.pt_id = 8089 group by pestproxshapes.pt_id, pestproxshapes.gid; Thanks, Derek --------------020106050109000408020101 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit

Hello,

I am using a array_agg to get a subset of data to use in a query. However, I can’t figure out how to get the data returned in the array_agg function to sort with an order by function. Any help would be appreciated. Here is my query as it is now. I am using postgresql 8:

select pestproxshapes.pt_id, pestproxshapes.gid, array_to_string(array_agg(coalesce(pestproxdata.agent || ' | Closest Agent (km): ' || round(pestproxdata.metric1,2) || ' | Acres: ' || round(pestproxdata.metric2, 2) || ' | count: ' || pestproxdata.count || ' | yr: ' || pestproxdata.year || ' | prob: ' || round(pestproxdata.prob,2) || ' | host: ' || pestproxdata.host, 'no agents reported within 75km')), '\n') as AGENTS from pestproxshapes left outer join pestproxdata on pestproxshapes.pt_id=pestproxdata.pt_id where pestproxshapes.pt_id = 8089 group by pestproxshapes.pt_id, pestproxshapes.gid;

Thanks,

Derek

--------------020106050109000408020101-- From tgl@sss.pgh.pa.us Tue Feb 28 04:15:56 2012 Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229]) by mail.postgresql.org (Postfix) with ESMTP id A81BB9552CF for ; Tue, 28 Feb 2012 00:15:56 -0400 (AST) Received: from sss.pgh.pa.us ([66.207.139.130]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1S2ETe-0001S9-Ny for pgsql-sql@postgresql.org; Tue, 28 Feb 2012 04:15:55 +0000 Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id q1S4Fbvh025515; Mon, 27 Feb 2012 23:15:37 -0500 (EST) To: jdmorgan cc: pgsql-sql@postgresql.org Subject: Re: array_agg order by In-reply-to: <4F4C4EF4.4000006@gmail.com> References: <4F4C4EF4.4000006@gmail.com> Comments: In-reply-to jdmorgan message dated "Mon, 27 Feb 2012 22:50:12 -0500" Date: Mon, 27 Feb 2012 23:15:37 -0500 Message-ID: <25514.1330402537@sss.pgh.pa.us> From: Tom Lane X-Pg-Spam-Score: -1.9 (-) X-Archive-Number: 201202/88 X-Sequence-Number: 36381 jdmorgan writes: > I am using a array_agg to get a subset of data to use in a > query.However, I can't figure out how to get the data returned in the > array_agg function to sort with an order by function.Any help would be > appreciated.Here is my query as it is now.I am using postgresql 8: Well, if you were using 9.0 or later, you could do this "right": select array_agg(foo order by bar), otherstuff from ..tables..; You can kind of get there in older versions with a sub-select: select array_agg(foo), otherstuff from (select foo, otherstuff from ..tables.. order by whatever) ss; However that's a bit shaky because you can't do very much in the outer query, like say grouping, without risking messing up the sort ordering. You have to keep a close eye on the EXPLAIN output for your query to make sure nothing re-sorts the data before it gets to the Aggregate step. regards, tom lane