Thnaks Cetin,
that gives me the 9 records I expect to see.


Peter


>It doesn't necessarily mean results are wrong. However for perfomance you might try to divide it in 2 SQLs :
>

>SELECT ;
>	pitype_id,;
>	MAX(expdate);
>	FROM PI ;
>	GROUP BY pitype_id ;
>    INTO CURSOR cc0	nofilter
>
>SELECT ;
>	a.pi_id,;
>	a.pitype_id,;
>	a.expdate ;
>	FROM PI a ;
>	inner join cc0 b on a.pitype_id = b.pitype_id ;
>		and a.expdate = b.expdate ;
>	WHERE eqtype_id=2 AND equip_id=2246 ;
>    INTO CURSOR cc1		
>

Cetin
>
>
>>Thanks Cetin,
>>That is a very interesting construct, but..
>>
>>It takes 640 seconds to return an empty cursor which is not correct.
>>
>>Peter
>>
>>
>>>>I need to retrieve the pi_id for the latest expdate by pitype. in vfp 7 I would do:
>>>>
>>>>

>>>>SET ENGINEBEHAVIOR 70
>>>>SELECT ;
>>>>	pi_id,;
>>>>	pitype_id,;
>>>>	MAX(expdate);
>>>>	FROM PI ;
>>>>	WHERE eqtype_id=2 AND equip_id=2246 ;
>>>>	GROUP BY 2;
>>>>	INTO CURSOR cc1
>>>>

>>>>
>>>>in vfp 8 that gives me a group by error
>>>>
>>>>

>>>>SET ENGINEBEHAVIOR 80
>>>>SELECT ;
>>>>	pi_id,;
>>>>	pitype_id,;
>>>>	MAX(expdate);
>>>>	FROM PI ;
>>>>	WHERE eqtype_id=2 AND equip_id=2246 ;
>>>>	GROUP BY 1,2;
>>>>	INTO CURSOR cc1
>>>>

>>>>
>>>>works but gives me all records because pi_id is the unique primary key (which i AM AFTER)
>>>>
>>>>
>>>>What to do?
>>>>
>>>>Peter
>>>
>>>
>>>

>>>SELECT ;
>>>	pi_id,;
>>>	pitype_id,;
>>>	expdate ;
>>>	FROM PI t1 ;
>>>	WHERE eqtype_id=2 AND equip_id=2246 ;
>>>          and expdate = ;
>>>         (select max(expdate) from pi t2 where t1.pitype_id = t2.pitype_id) ;
>>>	INTO CURSOR cc1
>>>

Cetin