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@omnicode.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)?