Re: field must appear in the GROUP BY clause or be used - Mailing list pgsql-general

[ drifting a bit off the thread topic, but just for completeness... ]

Bill Moran <wmoran@potentialtech.com> writes:
> I turned that over in my head a little and tried this:
> select id, max(name) from gov_capital_project group by id order by MAX(name);
> ...
> A little more playing around shows that this also works:
> select id, max(name) as name from gov_capital_project group by id order by name;

> Which will probably be a little faster since MAX() is evaluated less.

Actually I believe you'll get the exact same plan either way.  GROUP and
ORDER BY expressions are merged with any matching SELECT-list entries
during parsing.

In fact, as of (I think) 7.4, the executor detects and eliminates
duplicate aggregate-function calls even when the parser didn't.
So for instance this:
    SELECT max(x), max(x) + 1 FROM ...
will only run the MAX() aggregate once.

            regards, tom lane