From: | Bruno Wolff III <bruno(at)wolff(dot)to> |
---|---|
To: | Volkan YAZICI <yazicivo(at)ttnet(dot)net(dot)tr> |
Cc: | Everton Luís Berz <everton(dot)berz(at)gmail(dot)com>, pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Sorting aggregate column contents |
Date: | 2006-05-03 00:16:02 |
Message-ID: | 20060503001602.GC31652@wolff.to |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
On Wed, May 03, 2006 at 00:13:40 +0300,
Volkan YAZICI <yazicivo(at)ttnet(dot)net(dot)tr> wrote:
> On May 02 06:00, Everton Luís Berz wrote:
> > Is it possible to sort the content of an aggregate text column?
> >
> > Query:
> > select s.name, ag_concat(c.name) from state s
> > inner join city c on (c.idstate = s.idstate)
> > group by s.name
> > order by s.name;
>
> IMHO, you can receive results ordered by using a subselect:
>
> SELECT T.s_name, ag_concat(T.c_name)
> FROM (SELECT s.name, c.name
> FROM state AS s
> INNER JOIN city AS c ON (c.idstate = s.idstate)
> ORDER BY s.name, c.name) AS T (s_name, c_name)
> GROUP BY T.s_name;
Note that this is nonstandard, but is an intentional (but I am not sure if
it's documented) feature of Postgres. There can be some similar situations
where you need to use OFFSET 0 to prevent optimizations that will break the
ordering.
From | Date | Subject | |
---|---|---|---|
Next Message | Everton Luís Berz | 2006-05-03 00:24:28 | Re: Sorting aggregate column contents |
Previous Message | Volkan YAZICI | 2006-05-02 21:13:40 | Re: Sorting aggregate column contents |