Assuming that EFFYY , mm, dd are numeric, this would give you the Part# and date with the greatest date.

Select PART#, max(  EFFYY * 10000 + EFFMM * 100 + EFFDD)

from myfile group by Part#

Roughly this would do it.  But there is an issue if you have two sales in the same day. 

select Part#, price from myfile f join ( 

Select PART#, max(  EFFYY * 10000 + EFFMM * 100 + EFFDD) as date1 from myfile group by Part#)  d

on f.Part# = d.Part# and d.date1 = f. EFFYY * 10000 + f.EFFMM * 100 + f.EFFDD

Philp

I just could not get your statements to work. Thanks for the effort.

I ending using an SQL statement with UDF

With T1 AS                                                       
    (select lppn, max(LPEFYY * 10000 + LPEFMM * 100 + LPEFDD), 0 
       from KBM400MFG/FHLSTPRC a  group by lppn),                
                                                                 
     T2 AS                                                       
  (select PART, EFFDATE, LPBPRC from  macneil/tempprice          
      JOIN KBM400MFG/FHLSTPRC                                    
        on part = lppn                                           
       and EFFDATE = (LPEFYY * 10000 + LPEFMM * 100 + LPEFDD)),  
                                                                 
      T3 AS                                                      
  (Select DISTINCT(PART), LPBPRC from T2)                        
 select * from T3                                                

I'd use DIGITS() and CONCAT (and perhaps CHAR() if EFFDATE needs it) in order to avoid the arithmetic, but it should work either way.

I meant CTE (Common Table Expressions)

What is the purpose of [T1] in your CTE? I don't see how it's referenced anywhere.

Tom