Thread: adding "order by" to a "group by" query
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
AndrewWilliams | {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
Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org> schrieb: > > 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? 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;i | comma ---+---------1 | c, b, a (1 row) (I'm using the comma-aggregat - function from http://www.zigo.dhs.org/postgresql/#comma_aggregate) Hope that helps. 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°
On Sat, Dec 06, 2008 at 06:26:06PM +0100, Andreas Kretschmer wrote: > Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org> schrieb: > > > > 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? > > 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) -- http://www.critikart.net
On Sat, Dec 6, 2008 at 10:31 AM, Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org> wrote: > On Sat, Dec 06, 2008 at 06:26:06PM +0100, Andreas Kretschmer wrote: >> Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org> schrieb: >> > >> > 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? >> >> 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...
Scott Marlowe <scott.marlowe@gmail.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°
>(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_eventx 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?
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