Message-ID: <3E197A39.8070808@klaster.net>
Date: Mon, 06 Jan 2003 13:44:41 +0100
From: Tomasz Myrta <jasiek@klaster.net>
User-Agent: Mozilla/5.0 (Windows; U; Win 9x 4.90; en-US;
	rv:1.2b) Gecko/20021016
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: order by and aggregate
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

Hi
I created my own aggregate function working as max(sum(value))
It adds positive and negative values and finds maximum of this sum.
To work properly this function needs data to be sorted.

select
   maxsum(value)
from some_table
   order by some_field

doesn't work:
ERROR:  Attribute some_table.some_field must be GROUPed or used in an 
aggregate function


I found I can obey this using subselect:
select
   maxsum(X.value)
from
  (select value
   from some_table
   order by some_field) X

I can't create subselect, because I want to change this query into a 
view. In my case postgresql doesn't use indexes on subselect inside a view.

What should I do?

Regards,
Tomasz Myrta