Re: Group by & Distinct

raj Agarwal (rka.ra_at_worldnet.att.net) 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

There are 2 distinct DISTINCTs. One is the DISTINCT option of the SELECT clause. The other is the DISTINCT option of certain GROUP BY functions. I assume you are referring to SELECT DISTINCT, since the other is specifically intended to change the results of GROUP BY calculations. But even in this case, the DISTINCT operator can make a difference if you are not selecting the columns that you are grouping by. For instance,  

      select 1 from user_tables group by table_name;
      select distinct 1 from user_tables group by table_name;

Admittedly, these are stupid queries. But there may be some less stupid queries where DISTINCT makes a difference, if you are not selecting all the rows of the GROUP BY.

Donald A. Smith         No, that was JOHN Smith
eddas_at_huber.com         I have never met Pocahontas