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

On Sat, Dec 06, 2008 at 06:24:25PM +0000, John Lister wrote:
> >(still curious about the "must be used in an aggregate function" error
> >though... because I do use it in an aggregate)
>
> You're original query grouped on the person id and name, therefore you
> can only return (and order by) these functions or the result of an
> aggregate function on other columns (such as the array_accum function).
>
> I'm no expert, but I think the error is slightly misleading, normally
> you would order by the result of an aggregate function but maybe the
> parser does this implicitly for you sometimes. does
>
> select p.id_person, person_name(p), array_accum(distinct pt.type_fr)
> from person p
> left join person_to_event x using (id_person)
> left join person_type pt using (id_person_type)
> where person_name(p) ilike '%will%' group by
> p.id_person,person_name(p)
> order by 3;
>
> work for you?

Not quite. But thanks for your suggestion John: I just learned that one
can supply an index to an order clause.

Actually what I'd like to be able to do is: put the (say) 'actors' in
front of the list. The catch is that a person can have several
person_type's (through the person_to_event table: id_person, id_event,
id_person_type).

--
http://www.critikart.net