Re: SQL: discussion of GROUP BY clause

"Brian Dick" <bdick_at_home.com> wrote in message news:<CvRH7.5887$Xb7.43353_at_news1.wwck1.ri.home.com>...
> When I don't want some of the GROUP BY expressions in my result set, I
> usually write the SELECT..GROUP BY as a dynamic table. For example
>
> select sum_b
> from (select a, sum(b) sum_b from t group by a)

The above query could be simply computed as:

select sum(b) from t group by a

which is standard SQL. Even though b does not appear in the group by clause aggregates involving any column are always acceptable in the select clause.

However the trick you mention is not possible in the example I had originally posted where we had:

CREATE TABLE T(k1 INT, k2 INT, a INT, PRIMARY KEY (k1, k2))

and the query

(A) SELECT a FROM T GROUP BY k1 HAVING k2 = MAX(k2);

had to be rewritten as:

(B) SELECT a FROM T TA WHERE k2 = (SELECT MAX(k2) FROM T WHERE k1 = TA.k1)

output:
3
6
9

For instance consider the table:

 k1 | k2 | a
----+----+---

  1 |  1 | 1
  1 |  2 | 2
  1 |  3 | 3
  2 |  1 | 4
  2 |  2 | 5
  2 |  3 | 6
  3 |  1 | 7
  3 |  2 | 8
  3 |  3 | 9

If I were to include k2 and a in the having clause as the SQL standard mandates then the result would be totally different and all row values of a would be output, as expected, as groups would be distinguished by all three attributes. However, the original query (A) is intuitively understandable so SQL should accept it.

Regards,

Neil Received on Mon Nov 12 2001 - 19:32:07 CST