Re: adding "order by" to a "group by" query

On Sat, Dec 06, 2008 at 06:26:06PM +0100, Andreas Kretschmer wrote:
> Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org> schrieb:
> > 
> > But if I append this
> > 
> >      order by pt.type_fr = 'comédien';
> > 
> > I get this error:
> > 
> >     ERROR:  column "pt.type_fr" must appear in the GROUP BY clause or be used in an aggregate function
> > 
> > It seems I am using pt.type_fr in an aggregate function (array_accum()),
> > yet I get the error.
> > 
> > Is there a way to to have a certain pt.type_fr bubble up (or down) in my
> > search?
> 
> You can use a subquery like my example:
> 
> test=*# select i, comma(t) from (select distinct i,t from foo) bar group by i;
>  i |  comma
> ---+---------
>  1 | a, b, c
> (1 row)
> 
> Time: 0.554 ms
> test=*# select i, comma(t) from (select distinct i,t from foo order by t desc) bar group by i;

Thanks Andreas, that would be good solution.

(still curious about the "must be used in an aggregate function" error
though... because I do use it in an aggregate)

-- 
http://www.critikart.net