Re: SQL Question

["rick baird" <rick.baird@xxxxxxxxx>]

sorry I meant Jeff, not jef.  the code should be good.  I tested it.

On 12/1/06, rick baird <rick.baird@xxxxxxxxx> wrote:
> Jef - this will select one or all records in the file with the oldest
> date (if it's possible that the oldest date is duplicated)
>
> select * --- replace the asterisk with the fields you need --
>  from myfile a
>  join (select b.division,
>               b.account,
>               b.po#,
>               max(b.cdate) as chgdate
>          from myfile b
>      group by b.division,
>               b.account,
>               b.po#
>      order by b.division,
>               b.account,
>               b.po# ) b
>   on a.division = b.division
>  and a.account = b.account
>  and a.po# = b.po#
>  and a.cdate = chgdate
>
>
> On 12/1/06, Jeff Young <cooljeff913@xxxxxxxxx> wrote:
> > I have a history file with the following fields :
> > Division
> > Account
> > PO#
> > Date Changed YYMMDD
> > Date Changed Century  1/0
> > Cancel Date
> >  Old / New Code  O/N
> > .
> > .
> > .
> > .
> >
> > Is there a way using SQL to select the data from the record with the oldest date 
> for a given Division, Account & PO#.
> >
> > TIA,
> >
> > Jeff Young
> > Sr. Programmer Analyst
> > Dynax Solutions, Inc.
> > A wholly owned subsidiary of enherent Corp.
> > IBM -e(logo) server Certified Systems Exper - iSeries Technical Solutions V5R2
> > IBM  Certified Specialist- e(logo) server i5Series Technical Solutions 
> Designer V5R3
> > IBM  Certified Specialist- e(logo)server i5Series Technical Solutions 
> Implementer V5R3
> >
> >
> >
> > 
> ____________________________________________________________________________________
> > Cheap talk?
> > Check out Yahoo! Messenger's low PC-to-Phone call rates.
> > http://voice.yahoo.com
> > --
> > 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.
> >
> >