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
