Mark C. Stock wrote:
> "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
Mea culpa. Don't know where my head was.
--
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)
Received on Wed Jan 26 2005 - 13:26:42 CST
