Re: Group by & Distinct

Daniel Wyler wrote:
>
> raj Agarwal wrote:
> >
> > In a query that has a 'group by' , will a 'distinct' give any different
> > result?
> >
> > As a group by will group all same values into one row, I think the
> > distinct will not change the result set.
> >
> > Is this correct? If not correct would appreciate a sample query and
> > data sample where the above is not true.
> >
> > Thanks,
> >
> > raj
>
> I don't agree with you. The distinct is for the selection (if there
> are 5 rows of customer #1 he will only get one row with a distinct) ,
> the group by is about the result (if you ask for an avg(sales) on
> customer #1 you get the average of each of the 5 rows).
>
> Regards
>
> Daniel Wyler

I just did a little test:

v	n
-----	--------
x	1
x	2   <---- If distinct did cause a change, it would be in these
x	2   <---- two rows here
y	2

select v, sum(n) from test group by v;
v	n
-----	--------
x	5
y	2

select distinct v, sum(n) from test group by v;
v	n
-----	--------
x	5
y	2

If distinct was filtering out rows BEFORE the group by, the x total would have been 3, not 5. So evidently, distinct filters out the rows AFTER the group by (when they are all distinct anyway, so it has no additional effect)

-- 
-------------------------------------------------------
Ken Johnson                    Technical Consultant
Tapestry Computing, Inc.       http://www.tapestry.com