Re: Using an SQL index over DDS file?

Adding to what Michael and Chuck state, I seem to remember that the SQL
optimizer changes DISTINCT queries to GROUP BY, so perhaps Lloyd would like
to try Michael's suggestion and check if it offers a performance
improvement?

Regards,

Luis Rodriguez
IBM Certified Systems Expert — eServer i5 iSeries
--



On Mon, Jul 26, 2010 at 2:50 PM, CRPence <CRPbottle@xxxxxxxxx> wrote:

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.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.