you can have, in the select part of the statement, only columns that are in the group by part of the SQL statement or columns that are somehow aggregated . Please check sql reference ( eg. http://www.w3schools.com/sql/sql_groupby.asp )

On Thu, Sep 30, 2010 at 11:43 AM, Alex Gusev <flancer64@gmail.com> wrote:
 Hello,

I have created a table:

CREATE TABLE TEST (VAL1 VARCHAR(3) NOT NULL, VAL2 VARCHAR(3) NOT NULL);

Then I execute statement

SELECT * FROM TEST GROUP BY VAL1;

and I have an error:

ERROR 42Y36: Column reference 'TEST.VAL2' is invalid, or is part of an invalid expression.  For a SELECT list with a GROUP BY, the columns and expressions being selected may only contain valid grouping expressions and valid aggregate expressions.

What have I done wrong? The same statements are executed successfully on MySQL.


My sysinfo:

C:\Users\Alex>sysinfo
------------------ Java Information ------------------
Java Version:    1.6.0_21
Java Vendor:     Sun Microsystems Inc.
Java home:       C:\Program Files\Java\jdk1.6.0_21\jre
Java classpath:  C:\Work\app\db-derby-10.6.1.0-bin/lib/derby.jar;C:\Work\app\db-
derby-10.6.1.0-bin/lib/derbynet.jar;C:\Work\app\db-derby-10.6.1.0-bin/lib/derbyc
lient.jar;C:\Work\app\db-derby-10.6.1.0-bin/lib/derbytools.jar
OS name:         Windows Vista
OS architecture: x86
OS version:      6.0
Java user name:  Alex
Java user home:  C:\Users\Alex
Java user dir:   C:\Users\Alex
java.specification.name: Java Platform API Specification
java.specification.version: 1.6
java.runtime.version: 1.6.0_21-b06
--------- Derby Information --------
JRE - JDBC: Java SE 6 - JDBC 4.0
[C:\Work\app\db-derby-10.6.1.0-bin\lib\derby.jar] 10.6.1.0 - (938214)
[C:\Work\app\db-derby-10.6.1.0-bin\lib\derbytools.jar] 10.6.1.0 - (938214)
[C:\Work\app\db-derby-10.6.1.0-bin\lib\derbynet.jar] 10.6.1.0 - (938214)
[C:\Work\app\db-derby-10.6.1.0-bin\lib\derbyclient.jar] 10.6.1.0 - (938214)
------------------------------------------------------

Thanks a lot,

Alex.



--
"No Im sorrys, no apologies, no regrets..... "