Skip site navigation (1) Skip section navigation (2)

Re: order function in aggregate

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Richard Huxton <dev(at)archonet(dot)com>
Cc: Michael Toews <mwtoews(at)sfu(dot)ca>, pgsql-sql(at)postgresql(dot)org
Subject: Re: order function in aggregate
Date: 2008-07-30 17:45:18
Message-ID: 1117.1217439918@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-sql
Richard Huxton <dev(at)archonet(dot)com> writes:
> Michael Toews wrote:
>> This is where I get confused, since in the aggregate function, I have 
>> specified `SORTOP="<"`, which according to the documentation 
>> (http://www.postgresql.org/docs/8.3/interactive/sql-createaggregate.html) 
>> "must be equivalent to":
>> 
>> SELECT sub FROM aggr_test ORDER BY sub USING <; -- LIMIT 1;

> That's for things like min()/max() where you only want the first/last 
> value of some set. You want all of them.

Right: sortop is an assertion that the aggregate behaves like min() or
max(), not a request for the system to do something strange to the
aggregate's input.

> You could accumulate the values in an array and then sort that with the 
> final-func that create aggregate supports.

The traditional way to get presorted input to an aggregate is

select myagg(x) from (select whatever as x from ... order by ...) ss;

You can avoid that with an internal sort in the aggregate, as Richard
suggests, but it might not be worth the trouble --- and in any case
it'd be hard to sort internally on anything except the exact values
being aggregated.  With the subselect approach you can order on anything
at all.

			regards, tom lane

In response to

pgsql-sql by date

Next:From: Ryan WallaceDate: 2008-07-30 17:51:27
Subject: Re: Identifying which column matches a full text search
Previous:From: Richard HuxtonDate: 2008-07-30 17:28:28
Subject: Re: order function in aggregate

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group