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