Re: field must appear in the GROUP BY clause or be used

Mike Mascari wrote:
> Bill Moran wrote:
>
>> Hey all.
>>
>> I've hit an SQL problem that I'm a bit mystified by.  I have two
>> different
>> questions regarding this problem: why?  and how do I work around it?
>>
>> The following query:
>>
>> SELECT     GCP.id,
>>     GCP.Name
>>     FROM    Gov_Capital_Project GCP,
>>     WHERE TLM.TLI_ID = $2
>>     group by GCP.id
>>     ORDER BY gcp.name;
>>
>> Produces the following error:
>>
>> ERROR:  column "gcp.name" must appear in the GROUP BY clause or be
>> used in an aggregate function
>
> The reason the grouping requires either an attribute to be aggregated or
> apart of the group by list is that if it were not, an arbitrary value
> would have to be selected:

Thanks to everyone who responded.  All the replies have been very helpful.

Talking with the originator of the SQL statement, I came up with this:

select id, max(name) from gov_capital_project group by id order by name;
ERROR:  column "gov_capital_project.name" must appear in the GROUP BY clause or be used in an aggregate function

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);

Which finally works!  As far as I understand it, that query will supply the
same results as they were getting from MSSQL on the previous query.

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.

Now I'm starting to see (maybe) why the query worked under MSSQL.  the
MSSQL version had:

SELECT id as [ID], max(name) as [Name] from gov_capital_project group by id order by name;

I'm guessing that MSSQL is fuzzy enought to figure that "group by name" actually
means "group by [Name]"?

--
Bill Moran
Potential Technologies
http://www.potentialtech.com