Re: SQL Question

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.
>
>