Re: SQL error - please help.

On Tue, 23 Aug 2005, Bernard wrote:

> Dear Postgresql specialists
>
> I would like to seek help with a SQL query that was developed and
> tested with other SQL92 compliant databases.

IIRC, allowing select items that are not in the group by but are
functionally dependant on the group by columns was added in SQL99.

7.9 <query specification> (in the SQL92 draft) states "If T is a grouped
table, then each <column reference> in each <value expression> that
references a column of T shall reference a grouping column or be specified
within a <set function specification>" which is not the case in the query
below.

There's been talk about doing the SQL99 rules in the past, but noone's
stepped up to do it.

> 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