On 8/22/05 8:24 AM, "Bernard" <bht@xxxxxxxxxxxxx> wrote:
> 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
It simply means that you must include department.name in the group by clause
like:
GROUP BY department.pk, department.name
The rest of the query looks OK.
Sean
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
