RE: group by issue...??

[Paul DuBois]

At 16:58 -0700 9/10/04, bruce wrote:
>paul....
>
>i do a
>
>select ID from hTBL group by type
>
>and i still only get a single row for each type, where i would expect to get
>the ~5000 rows, grouped around the 3 different types.

Hmm.  I see my previous answer said "with" and should have said "without".

If you use GROUP BY without selecting the value of any aggregate functions,
you achieve the same effect as SELECT DISTINCT.


>
>what's going on????
>
>thanks...
>
>-bruce
>
>
>-----Original Message-----
>From: Paul DuBois [mailto:paul@stripped]
>Sent: Friday, September 10, 2004 4:36 PM
>To: bedouglas@stripped; mysql@stripped
>Subject: Re: group by issue...??
>
>
>At 16:27 -0700 9/10/04, bruce wrote:
>>hi...
>>
>>if i do this...
>>
>>select
>>h1.itemID as hitem,
>>h1.process as process,
>>h1.status as status,
>>h1.tblType as tbl,
>>h1.date as date
>>from historyTBL as h1
>>    where (h1.tblType = '3' or h1.tblType = '4');
>>
>>i get a results table with ~5000 rows...
>>
>>if i add the 'group by' sql,
>>
>>select
>>h1.itemID as hitem,
>>h1.process as process,
>>h1.status as status,
>>h1.tblType as tbl,
>>h1.date as date
>>from historyTBL as h1
>>    where (h1.tblType = '3' or h1.tblType = '4')
>>group by tblType;
> <<<<<<<<<<<<<<<<
>>
>>i get a single line for each tblType...
>>
>>i was under the impression that i should get the same number of rows
>>returned, but that they would be grouped together.....
>>
>>can someone provide an explanation as to how this should work, and what's
>>going on????
>
>If you use GROUP BY with selecting the value of any aggregate functions,
>you achieve the same effect as SELECT DISTINCT.


-- 
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com