MS SQL query Q

[rmatthew]

I have a query that goes something like this.

select table1.field1 as field1,table1.field2 as field2 from table1 group by field1

SQL Server 2000 gives me an error that the GROUP BY must be in the select list.

I must use this syntax if possible (Alias fields).

Any ideas as to how to fix ?

Thanks in advance.

 

[jfackler]

If the dbms has problems resolving the proper field names that exist in multiple tables the query processor may become confused and fail to execute. "Field1" is pretty generic. Youll probably need to use the fully qualified name.

Jon

 

[rmatthew]

The query is from the same table - maybe this clarifies alittle:

Select plants.name as Plant Name, plants.address as Address from plants group by Plant Name

Grouping by the alias causes the error.

 

[jfackler]

Put your alias in brackets: i.e. [Plant Name] not quotes in sql server.

Jon

 

[zy_abc]

Hi,

Why dont you try

Select plants.name as Plant Name, plants.address as Address from plants 
group by plants.name,plants.address