Re: SQL basic questions

["Jake M" <jakeroc@xxxxxxxxx>]

try this...

'select acct, model from lib.file group by acct, model'

hth,

Jake.

On 4/24/06, steema@xxxxxxxxxxxxx <steema@xxxxxxxxxxxxx> wrote:
>
> 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.
>
>