SQL: discussion of GROUP BY clause

From: Neil Zanella <nzanella_at_garfield.cs.mun.ca>
Date: Sat, 10 Nov 2001 22:08:45 -0330
Message-ID: <Pine.LNX.4.30.0111102152200.14584-100000@garfield.cs.mun.ca>

Hello,

The SQL standard imposes that when using the GROUP BY clause all attributes appearing in the SELECT clause must also appear in the GROUP BY clause. This design decision has its good reasons for being there but there are cases in which this is a bit of a hinderance. For instance consider the following:

CREATE TABLE T(

  k1 INT,
  k2 INT,
  a1 INT,
  a2 INT,
  a3 INT,

  PRIMARY KEY (k1, k2)
);

SELECT a1, a2, a3
FROM T
GROUP BY k1,
HAVING k2 = MAX(k2);

SQL considers this query to be wrong. However if SQL would allow this then we would indeed have exactly one row of output in every possible instance of table T. This is because k1 and k2 form a primary key and for each group consisting of a fixed value for k1 there is exactly one value for k2 which means that the pair (k1, k2) and hence a1, a2, and a3 are uniquely determined. Thus in theory this query could be parsed with no complaints from the DBMS but the SQL standard forbids it.

Now if we were to remove the HAVING clause there is no doubt that the above query would be wrong as we would have multiple values of a1, a2, and a3 for each group.

The above query can be accomplished in legal SQL using a subselect:

SELECT a1, a2, a3
FROM T TA
WHERE k2 = (SELECT MAX(k2) FROM T WHERE k1 = TA.k1)

Regards,

Neil Received on Sat Nov 10 2001 - 19:38:45 CST