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

Scott Marlowe <scott.marlowe@gmail.com> schrieb:
> >> 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)
> 
> You can order by the same thing you selected:
> 
> select sum(i) from ... group by j order by sum(i)
> OR
> select sum(i) from ... group by j order by 1  --  1 stands for the
> first select list item...

Not in this case:

test=# select  i, comma(distinct t ) from foo group by i order by
comma(t) desc;i |  comma
---+---------1 | a, b, c
(1 row)


doesn't work as desired.


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°