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
