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

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.