RE: SQL basic questions

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

Select distinct acct, model from mytable

Should work fine unless you've got a 
12806  001  BBB


Then the same question applies, do you want
12806 AAA

Or

12806 BBB

Or 
12806 AAA
12806 BBB

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 
> steema@xxxxxxxxxxxxx
> Sent: Monday, April 24, 2006 10:37 AM
> To: Midrange Systems Technical Discussion
> Subject: RE: SQL basic questions
> 
> I did what I needed the tedious way, 2 programs, 1 workfile.
> 
> lets say you have an account.
> 
> 12806  it has stores and models
> acct    store    model
> 12806   0001     AAA
> 12806   0002     AAA
> 
> I just want 12806 AAA don't care about the store.
> 
> I would rather do this in one SQL statement.
> 
> > How do you expect to pick the one model to display?
> >
> > Easiest would be to use min/max
> >
> > Select acct, min(model) from mytable
> > Group by acct
> >
> > If you want the "first" model with a given account, that 
> would be harder
> > to do but it can be done.  The key would be determining the 
> definition
> > of first you want to use.
> >
> >
> > 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
> >> steema@xxxxxxxxxxxxx
> >> Sent: Monday, April 24, 2006 9:20 AM
> >> To: midrange-l@xxxxxxxxxxxx
> >> Subject: SQL basic questions
> >>
> >> HI I want to be able to use the Select Distinct feature on a
> >> key level.
> >> I have a file w/ Acct and model #, and I want to list only
> >> one model per
> >> acct. instead when i do Select Distinct currently, I get many per
> >> Acct/model. Is there a way to make this break on the key?
> >>
> >>
> >> --
> >> 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.
> >
> >
> 
> -- 
> 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.
> 
>