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

Re: order function in aggregate

From: Richard Huxton <dev(at)archonet(dot)com>
To: Michael Toews <mwtoews(at)sfu(dot)ca>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: order function in aggregate
Date: 2008-07-30 17:28:28
Message-ID: 4890A4BC.1030404@archonet.com (view raw or flat)
Thread:
Lists: pgsql-sql
Michael Toews wrote:
> Now, I would like to group a summary of the strings in "sub", but they 
> must be ordered alphabetically. Here I attempt the SQL:
> 
>   select commacat(sub), grp from aggr_test group by grp;
> 
> However, on my system it will output the "commacat" column as:
> 
>   "one, two, three";"pom pom"
>   "two, three, one";"la la"
> 
> (notice that the items in the first column are ordered differently, 
> since the input values were entered in non-alphabetically)
> 
> 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.

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


-- 
   Richard Huxton
   Archonet Ltd

In response to

Responses

pgsql-sql by date

Next:From: Tom LaneDate: 2008-07-30 17:45:18
Subject: Re: order function in aggregate
Previous:From: Emi LuDate: 2008-07-30 15:32:08
Subject: Re: Get day name(Mon, Tue... Sun) and day number (1, 2...7) from a date

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