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

Scott Marlowe <scott(dot)marlowe(at)gmail(dot)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°