From: | Omar Eljumaily <omar2(at)omnicode(dot)com> |
---|---|
To: | |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Why does "group by" need to match select fields? |
Date: | 2007-03-01 00:57:08 |
Message-ID: | 45E624E4.9080808@omnicode.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
OK, I see what's going on. I can have more than one max(amount) with
the same amount and payee. Thanks so much. Like I said, it's sort of
dogged me off and on many times.
Thanks.
Bill Moran wrote:
> Omar Eljumaily <omar2(at)omnicode(dot)com> wrote:
>
>> 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?
>>
>
> Because it's ambiguous. If you're grabbing max() for amount, which
> id tuple do you want?
>
> Perhaps the way you're storing your data, those answers aren't ambiguous,
> but the database doesn't know that. Take this query as an example:
>
> select max(amount), max(checknumber), payee from checks group by payee;
>
> In that case, the highest checknumber and the highest check amount
> probably won't come from the same tuple. If you were to throw in
> there:
>
> select max(amount), max(checknumber), payee, id from checks group by payee;
>
> Which id does it give you? The one that matches max(amount) or the one
> that matches max(checknumber)?
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Paul Lambert | 2007-03-01 01:06:44 | Re: PG periodic Error on W2K |
Previous Message | Joshua D. Drake | 2007-03-01 00:52:15 | Re: PG periodic Error on W2K |