Skip site navigation (1) Skip section navigation (2)

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

From: "Scott Marlowe" <scott(dot)marlowe(at)gmail(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: Re: adding "order by" to a "group by" query
Date: 2008-12-06 17:40:43
Message-ID: dcc563d10812060940q73648b1oa00c8b174804dc24@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-sql
On Sat, Dec 6, 2008 at 10:31 AM, Louis-David Mitterrand
<vindex+lists-pgsql-sql(at)apartia(dot)org> wrote:
> On Sat, Dec 06, 2008 at 06:26:06PM +0100, Andreas Kretschmer wrote:
>> Louis-David Mitterrand <vindex+lists-pgsql-sql(at)apartia(dot)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...

In response to

Responses

pgsql-sql by date

Next:From: Andreas KretschmerDate: 2008-12-06 18:23:25
Subject: Re: adding "order by" to a "group by" query
Previous:From: Louis-David MitterrandDate: 2008-12-06 17:31:27
Subject: Re: adding "order by" to a "group by" query

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group