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

From: Bruno Wolff III <bruno(at)wolff(dot)to>
To: Omar Eljumaily <omar2(at)omnicode(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Why does "group by" need to match select fields?
Date: 2007-03-02 22:08:43
Message-ID: 20070302220843.GA28490@wolff.to
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On Wed, Feb 28, 2007 at 16:19:02 -0800,
Omar Eljumaily <omar2(at)omnicode(dot)com> wrote:
> 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?

While the DISTINCT ON approach is probably best if you can live with a
Postgres specific solution, the general way to do this is use the group by
query to get a set of primary keys with aggregates and then you join this
back to the original table to get the other data.

Some databases will also recognize that you are grouping by a candidate
key and allow you to specify normal columns since they must all have the
same value for rows with the same candidate key value. Unfortunately Postgres
doesn't do that now.

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Jeff Davis 2007-03-02 22:27:47 Re: How often do I need to reindex tables?
Previous Message Bill Moran 2007-03-02 22:01:38 Re: I'd love to know what the rest of this error message is.