Re: Simple query that consumes all temporary space.

From: DA Morgan <damorgan_at_x.washington.edu>
Date: Wed, 26 Jan 2005 10:59:01 -0800
Message-ID: <1106765787.108318@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)

Received on Wed Jan 26 2005 - 12:59:01 CST