RE: SQL - selecting issue

["Wilt, Charles" <CWilt@xxxxxxxxxxxx>]

Disregard, I read your request wrong.

I'll post again.

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 Wilt, Charles
> Sent: Wednesday, October 04, 2006 8:14 AM
> To: Midrange Systems Technical Discussion
> Subject: RE: SQL - selecting issue
>
> With dups as (select premise, meter, count(*)
>               from file
>               group by premise, meter
>               having count(*) > 1
>              )
> Select premise, meter
> > From file
> Where premise in (select premise from dups)
>
>
> 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 Smith, Mike
> > Sent: Wednesday, October 04, 2006 8:07 AM
> > To: midrange-l@xxxxxxxxxxxx
> > Subject: SQL - selecting issue
> >
> > Given the following file 
> > Premise     Seq #           Meter
> > X           01              99999
> > X           02              99999
> > Y           01              99998
> > Y           02              99998
> > Z           01              99999
> >
> > I would like to create an sql statement that will identify different
> > premises with the same meter#
> > To get a result of 
> > X                           99999
> > Z                           99999
> >
> > Premise X has the same meter twice, but since it is only 1 
> premise it
> > would only be counted once.  
> >
> > Can this be done?
> >
> > Michael Smith
> > iSeries.mySeries.
> >
> > -- 
> > 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.
>
> -- 
> 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.