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--