>Hi Mike,
>
>First, what are you MAX()-ing the effective date against in the second output field? There's nothing in the second value of the MAX() function...
>
>Second, have you tried a subquery in a WHERE clause, something like this:
>
>

SELECT ;
>    parts.part_pk, ;
>    NVL( price_history.Effective, DTOT({}) ) AS Effective_Date, ;
>    NVL( price_history.Price, 0000000.00 )   AS Current_Price ;
>  FROM parts ;
>  LEFT OUTER JOIN price_history ;
>    ON  parts.part_pk = price_history.part_fk ;
>  WHERE price_history.Effective IN ;
>    ( SELECT TOP 1 Effective FROM price_history ORDER BY 1 DESC ) ;
>  GROUP BY 1

I don't think this is going to work. The subquery will get the latest effective date for the entire table. If all the prices change onthe same day, then that's okay; however, that may not be the case. I think you need two separate selects:

select ;
   parts.part_pk, ;
   max(nvl(price_history.effective,dtot({}))) as Effective_Date ;
from parts ;
   left outer join price_history on ;
   parts.part_pk = price_history.part_fk ;
group by parts.part_pk into c_temp

select ;
   c_temp.part_pk, ;
   c_temp.Effective_Date, ;
   nvl(price_history.Price,0000000.00) as Current_Price ;
from c_temp ;
   left outer join price_history on ;
   c_temp.part_pk = price_history.part_fk ;
where c_temp.Effective_Date = price_history.effective

BTW, the reason you were getting screwed up results was because of the number of return fields in your SQL select. When you use an aggregate function with a GROUP BY clause VFP will essentially group everything together based on your key and SUM, AVG, whatever. But in order to do this it has to scan all the records that have the key. So you get the aggregate plus the values of the last record (physically) in the group.