Re: Simple query that consumes all temporary space.

"DA Morgan" <damorgan_at_x.washington.edu> wrote in message news:1106765787.108318_at_yasure...
> Jaap W. van Dijk wrote:
>
>> On Tue, 25 Jan 2005 16:02:50 -0800, DA Morgan
>> <damorgan_at_x.washington.edu> wrote:
>>
>> <snip>
>>
>>>You can't use GROUP BY and HAVING clauses without an aggregation.
>>
>> <snip>
>>
>> Why not? You don't have to specify an aggregate function in the SELECT
>> list of a GROUP BY select. Without a HAVING clause that is
>> functionally the same as specifying DISTINCT. The HAVING clause just
>> lets you be more selective after the grouping.
>>
>> The statement functionally just does what the OP wants.
>>
>> Jaap.
>
> Here is the OP's SQL statement:
>
> SELECT T1.LINE LINE1, T2.LINE LINE2 FROM T T1, T T2
> WHERE T2.LINE > T1.LINE
> AND T1.TXT = T2.TXT
> GROUP BY T1.LINE, T2.LINE
> HAVING COUNT(*) >= 3
>
> Define the group? There is none.
>
> If it were written as:
>
> SELECT T1.LINE LINE1, T2.LINE LINE2, COUNT(*)
> FROM T T1, T T2
> WHERE T2.LINE > T1.LINE
> AND T1.TXT = T2.TXT
> GROUP BY T1.LINE, T2.LINE
> HAVING COUNT(*) >= 3
>
> it would make sense.
> --
> Daniel A. Morgan
> University of Washington
> damorgan_at_x.washington.edu
> (replace 'x' with 'u' to respond)

sorry, daniel

you don't need a the count(*), or any other aggregate function in SELECT list in order to have a group

the group is simply defined by the GROUP BY, the HAVING clause is very, very often used to filter the groups (in this statement the 2 LINE columns) without exposing any grouping criteria

here's a simpler example:

"show me all the department and job combinations that have a per job headcount = 1, but don't show me the headcount because i don't need it"

SQL> select deptno, job
  2 from emp
  3 group by deptno, job
  4 having count(*)=1
  5 /

    DEPTNO JOB
---------- ---------

        10 CLERK
        10 MANAGER
        10 PRESIDENT
        20 MANAGER
        30 CLERK
        30 MANAGER

it's really not that unusual, and is certainly very legal and very SQL-101

++ mcs Received on Wed Jan 26 2005 - 13:15:39 CST