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

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