Re: Using an SQL index over DDS file?

[CRPence <CRPbottle@xxxxxxxxx>]

  I can perform many queries where the results are the same set, 
irrespective of the choice of the following statements:

    SELECT DISTINCT C1, C2, ... Cn
    FROM LIBNAME.TABLENAME

    SELECT          C1, C2, ... Cn /* Note: No aggregates */
    FROM LIBNAME.TABLENAME
    GROUP BY        C1, C2, ... Cn

  I am unable to produce however, any example where the two result 
sets are different.  Can you give an example where the result, other 
than order of rows, is not the same set of rows [given the same 
run-time sort sequence is in effect] for those two statements?

Regards, Chuck

On 26-Jul-2010 11:56, Charles Wilt wrote:
> Actually no it won't.
>
> Select distinct fld1, fl2
> from file
>
> would give one row in the results set for every combination of
> fld1, fld2
>
> Select fld1, fl2
> from file
> group by fld1, fld2
>
> would give you as many rows as the original table, with
> duplicates for the rows with the same fld1, fld2.
>
> In order for a solution using GROUP BY to work, you also need an
> aggregate function of some sort...
>
> Select fld1, fl2, count(*) as theCount
> from file
> group by fld1, fld2
>
> On Mon, Jul 26, 2010 at 2:33 PM, Schutte, Michael Dwrote:
> > One suggestion...
> >
> > Do a select  group by query versus a select distinct.
> >
> > Select  fld1, fld2
> > From file
> > Group by fld1, fld2
> >
> > Would give the same requests as
> >
> > Select distinct fld1, fld2
> >
> > Just try to see if VE gives a different answer.