Re: SQL - selecting issue

[Alfredo Delgado <adelgado@xxxxxxxxxxxxxxxxx>]

You're right, I glossed over that. I'd have used different notation but 
it'd execute the same way as the statements you provided. The index 
adviser in visual explain suggested creating a binary radix index over 
meter, premise. It replaced a table scan / temp hash table / temp hash 
table 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, Meter   
> FROM mike               
> GROUP BY Premise, Meter 
> HAVING count( Seq ) > 1 
>
> PREMISE  METER 
>   X     9,999 
>   Y     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 the
> > > energizer bunny.   
> > > Query running. 0 records selected, 28788150 processed. 
> > >
> > > Michael Smith
> > > iSeries.mySeries.
> > >
> > >
> > >