Why does "group by" need to match select fields?

From: Omar Eljumaily <omar2(at)omnicode(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Why does "group by" need to match select fields?
Date: 2007-03-01 00:19:02
Message-ID: 45E61BF6.5090102@omnicode.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Sorry if this isn't exactly postgresql specific. I periodically run
into this problem, and I'm running into it now. I'm wondering if
there's something about "group by" that I don't understand. As an
example what I'd want to do is return the "id" value for the check to
each payee that has the highest amount. It seems like there's no
problem with ambiguity in logic, but postgresql + other sql servers balk
at it. The group by fields need to explicitly match the select fields
with the exception of the aggregate function(s?).

create table checks
{
id serial,
payee text,
amount double
};

select max(amount), payee, id from checks group by payee;

Why won't the above work? Is there another way to get the id for the
record with the highest amount for each payee?

Thanks.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Bill Moran 2007-03-01 00:24:35 Re: why can't I increase shared buffers to higher value?
Previous Message Dino Vliet 2007-02-28 22:40:55 why can't I increase shared buffers to higher value?