[SQL] adding "order by" to a "group by" query

[Louis-David Mitterrand]

Hi,

This query:

        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);

returns:

 id_person |     person_name      |          array_accum          
-----------+----------------------+-------------------------------
       181 | William Eggleston    | {comédien}
       200 | William H.Macy       | {comédien}
       242 | William Nicholson    | {auteur}
       309 | William Friedkin     | {réalisateur}
       439 | William Shakespeare  | {auteur}
       591 | William Christie     | {musicien}
       786 | Paul Andrew Williams | {réalisateur}
      1015 | William Mesguich     | {comédien,"metteur en scène"}

But if I append this

         order by pt.type_fr = 'comédien';

I get this error:

        ERROR:  column "pt.type_fr" must appear in the GROUP BY clause or be 
used in an aggregate function

It seems I am using pt.type_fr in an aggregate function (array_accum()),
yet I get the error.

Is there a way to to have a certain pt.type_fr bubble up (or down) in my
search?

Thanks,

-- 
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