Skip site navigation (1) Skip section navigation (2)

Peripheral Links

Header And Logo

PostgreSQL
| The world's most advanced open source database.

Site Navigation

Search for
  Advanced Search

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



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?

Thanks.




Home | Main Index | Thread Index

Privacy Policy | PostgreSQL Archives hosted by Command Prompt, Inc. | Designed by tinysofa
Copyright © 1996 – 2008 PostgreSQL Global Development Group