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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
