RE: Sql Question

If you're looking for the one record with the highest date, try:

select * from file1 f1 inner join file2 f2                  
       on f1.acctno = f2.acctno                             
       where f2.acctno not in (select acctno from file2     
                                      where year(fldz)>2000)
       and fldz = (select max(fldz) from file2 f3           
                          where f2.acctno = f3.acctno)      

Buck Calabro
Aptis; Albany, NY

> -----Original Message-----
> From: Gade_R_Reddy@consecofinance.com 
> Sent: Wednesday, December 15, 1999 3:52 PM
> To:   RPG400-L@midrange.com
> Subject:      Re: Sql Question
> 
>  This Sql works fine but can i get only second record for the acctno 456 (
> beacuse 1999-12-31 is recent record for that acctno)
> 
> Thanks Much.
> Gade.
> 
-snip-

> File1
> Acctno    Fld1 Fld2 Fld3
> 123  a    a    a
> 456  b    b    b
> 
> File2
> Acctno    FldX FldY FldZ
> 123  a1   b1   19991011
> 123  a1   b1   19991105
> 123  a1   b1   20020106
> 456  a2   b2   19991001
> 456  a2   b2   19991231
> 
-snip-

> So if i run the sql statement with the above data i want the output to be
> 
>  AcctNo        FldZ
> 456       19991231 ( because the two records has FldZ value < 20000000 but
> in acctno 123 we have one record whose FldZ value > 20000000 )
> 
+---
| This is the RPG/400 Mailing List!
| To submit a new message, send your mail to RPG400-L@midrange.com.
| To subscribe to this list send email to RPG400-L-SUB@midrange.com.
| To unsubscribe from this list send email to RPG400-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---