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

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;i |  comma
---+---------1 | c, b, a
(1 row)


(I'm using the comma-aggregat - function from
http://www.zigo.dhs.org/postgresql/#comma_aggregate)


Hope that helps.

Andreas
-- 
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°