From: | Bernard <bht(at)actrix(dot)gen(dot)nz> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | SQL error - please help. |
Date: | 2005-08-22 12:24:53 |
Message-ID: | qhejg11pcfmknb3r94a75md3cjeo4hhdb0@4ax.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Dear Postgresql specialists
I would like to seek help with a SQL query that was developed and
tested with other SQL92 compliant databases.
Please examine the following testcase and the result that I get:
# su postgres
$ psql -d mydb -U postgres
CREATE TABLE DEPARTMENT(PK INT NOT NULL, NAME TEXT NOT NULL);
ALTER TABLE DEPARTMENT ADD PRIMARY KEY(PK);
CREATE TABLE PROJECT(PK INT NOT NULL, DEPARTMENT_FK INT NOT NULL, NAME
VARCHAR(30) NOT NULL, VALUE INT NOT NULL);
ALTER TABLE PROJECT ADD PRIMARY KEY(PK);
INSERT INTO DEPARTMENT(PK,NAME)VALUES(1,'Human Resources');
INSERT INTO DEPARTMENT(PK,NAME)VALUES(2,'Tax');
INSERT INTO
PROJECT(PK,DEPARTMENT_FK,NAME,VALUE)VALUES(1,1,'Head-Hunt',1000);
INSERT INTO
PROJECT(PK,DEPARTMENT_FK,NAME,VALUE)VALUES(2,1,'Redundancy',100);
INSERT INTO
PROJECT(PK,DEPARTMENT_FK,NAME,VALUE)VALUES(3,2,'Avoidance',1000);
INSERT INTO
PROJECT(PK,DEPARTMENT_FK,NAME,VALUE)VALUES(4,2,'Charity',100);
INSERT INTO
PROJECT(PK,DEPARTMENT_FK,NAME,VALUE)VALUES(5,2,'Lobbying',10000);
SELECT
DEPARTMENT.PK,
DEPARTMENT.NAME,
MIN(PROJECT.VALUE)AS RATING
FROM DEPARTMENT,
PROJECT
WHERE DEPARTMENT.PK=PROJECT.DEPARTMENT_FK
GROUP BY DEPARTMENT.PK
ORDER BY DEPARTMENT.PK;
ERROR: column "department.name" must appear in the GROUP BY clause or
be used in an aggregate function
... End of testcase
I have looked up this error message in the mailing list archives and
found a case with a plausible explanation (ambiguity) but I can't see
how this explanation would apply to the case under discussion.
Any help would be highly appreciated.
Regards
Bernard
From | Date | Subject | |
---|---|---|---|
Next Message | Gnanavel S | 2005-08-22 12:25:06 | Re: Rules UPDATE |
Previous Message | Bruno Almeida do Lago | 2005-08-22 11:55:00 | OCFS released as GPL |