Re: SQL - selecting issue -- MIDRANGE-L

You're right, I glossed over that. I'd have used different notation butit'd execute the same way as the statements you provided. The indexadviser in visual explain suggested creating a binary radix index overmeter, premise. It replaced a table scan / temp hash table / temp hashtable probe with an index probe. That about halved the execution time.I'd be interested in the performance results if Mike decides to try it out.


SELECT DISTINCT Premise, Meter
FROM ALFREDLIB.PREMISE
WHERE Meter IN (    SELECT Meter
                   FROM ALFREDLIB.PREMISE
                   GROUP BY Meter
                   HAVING count( distinct Premise ) > 1 )

Thanks,
Alfred

Wilt, Charles wrote:

No it didn't.
SELECT Premise, MeterFROM mikeGROUP BY Premise, MeterHAVING count( Seq ) > 1PREMISE METERX 9,999Y 9,998
He wants:
PREMISE  METER
  X     9,999
  Z     9,999

He's looking for duplicate meter numbers across premises.

Your statement gives premises were the same meter appears twice.  Not
the same thing.


HTH,

Charles Wilt
--
iSeries Systems Administrator / Developer
Mitsubishi Electric Automotive America
ph: 513-573-4343
fax: 513-398-1121
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Alfredo Delgado
Sent: Wednesday, October 04, 2006 9:45 AM
To: Midrange Systems Technical Discussion
Subject: Re: SQL - selecting issue

This worked for me on the test data you provided:

SELECT Premise, Meter
FROM ALFREDLIB.PREMISE
GROUP BY Premise, Meter
HAVING count( Sequence ) > 1

Thanks,
Alfred

Smith, Mike wrote:
Charles,
Yes I must have had a typo of some sort. I copied from your
last email
and it is working and working and working.  I'm still waiting on
results.   This may work, but I'm not sure it is going to be the
ultimate solution. My file has approximately 67,000 records.This was a recent copy as it still keeps on going- much like theenergizer bunny.Query running. 0 records selected, 28788150 processed.
Michael Smith
iSeries.mySeries.