Aggregate Function Error ???

[Scorp Scorp]

Am in the following cenario:

Table: tb_test
asset and Desc are of text type feilds.

----------------------
prt1 | prt2 | asset | Desc
----------------------
 1      1      11      xxx
 2      1      21      yyy
 1      2      12      zzz
 1      1      11      xxx
 2      1      21      yyy
============================
 

What i want to get (syntax):
Select asset and desc from table Order By asset

AM trying this sql query:
[code]
Select asset, desc from tb_test GROUP BY asset
[code/]

Am getting desc is not part of aggregate function, which am not sure why this error showing, i beleive it shouldnt. since always the values of the desc reveal same values for the corresponding asset value.

Any body can give a hint ??

[VijaySofist]

Hi,

Actually Desc is a Keyword in SQL. If you want to to use the Query to Select that Column named desc, try the Code below

Select [asset], [desc] from tb_test GROUP BY asset

Regards
Vijay.R

[Scorp Scorp]

Thanks Vijay , but i mentioned Desc just a shortcut to my Description field, sorry.

Its not the key word issue.
thanks any way

[Rabbit]

Use a distinct clause instead of group by.

[Scorp Scorp]

hey rabbit
but this will not solve , if later i need to get SUM of two feilds for each record and present it as total for each asset, thats why am using group by.

[Rabbit]

Then you need to either group by desc as well or use an aggregate function on desc.

[Scorp Scorp]

i tried :

Select asset, desc from tb_test GROUP BY asset order by asset 

same error
also tried

Select asset, desc from tb_test GROUP BY asset order by desc

same error.


up to my knowledge , if the value of the desk returned per asset are different than each other then it generates an error. while if they are equal , which is the case , error should not generate.
ie: for all asset 11 the desc is xxx

[Rabbit]

You're ordering by desc? I said you need to group by desc. If you group by it, you may need to include an arbitrary aggregate field. Or you could use an aggregate function on desc and bypass the group by, even if technically you don't want to aggregate on it.

[Scorp Scorp]

I tried all combinations in order and group through asset and desc ... if by lcuk to be true, but non broght up a result.

Can you type in what will be the statment with the use of an aggregate function.

First time use bypass.

As reports get more complex, sqls giving more headaches , i have a more complex sql st. i will post in onother thread , may be ill find who can help.

[Rabbit]

Select asset, Max(desc) from tb_test GROUP BY asset

[Scorp Scorp]

Rabbit , it worked , great.... thaks alot .