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

On Feb 28, 2007, at 6:57 PM, Omar Eljumaily wrote:

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

Omar, note that in many cases you can do:

select c.amount, c.payee, c.id
from checks c
where c.amount = (select max(amount)
from checks
where payee=c.payee);

erik jones <erik(at)myemma(dot)com>
sofware developer
615-296-0838
emma(r)