>>>>>>I have trust in this
>>>>>>

>>>>>>SELECT TI.cTrans_fk, TI.cInsurances_fk;
>>>>>>    FROM (select curTemp.cTrans_pk , max(T.iSequence_Number) iSequence_Number;
>>>>>>	    	from curTemp JOIN Trans_Insurances T ON curTemp.cTrans_pk = T.cTrans_fk ;
>>>>>>		    GROUP BY 1) X ;
>>>>>>		    JOIN Trans_Insurances TI ;
>>>>>>                 ON TI.cTrans_fk = X.cTrans_pk AND TI.iSequence_Number = X.iSequence_Number;
>>>>>> into cursor curTemp1
>>>>>>

>>>>>
>>>>>Seems to be a little bit faster, but still quite slow...
>>>>
>>>>post sys(3054) and current exec time
>>>

>>>Time: 3.804
>>>SELECT TI.cTrans_fk as cTrans_pk, ;
>>>TI.cInsurances_fk ;
>>>FROM (select curTemp.cTrans_pk, max(T.iSequence_Number) iSequence_Number ;
>>>from curTemp JOIN Trans_Insurances T ;
>>>ON curTemp.cTrans_pk = T.cTrans_fk GROUP BY 1) X ;
>>>JOIN Trans_Insurances TI ;
>>>ON TI.cTrans_fk = X.cTrans_pk AND ;
>>>TI.iSequence_Number = X.iSequence_Number into cursor curTemp1
>>>
>>>Rushmore optimization level for intermediate result: none
>>>Rushmore optimization level for table t: none
>>>Joining intermediate result and table t using index tag Ctrans_fk
>>>Rushmore optimization level for intermediate result: none
>>>Rushmore optimization level for table ti: none
>>
>>
>>Joining intermediate result and table ti using temp index && !!!! temp index !!!!!
>>
>>

>>>
>>>I'm running everything locally with SET DELETED ON.
>>
>>for testing try and post sys(3054)
>>

>>
>>SELECT TI.cTrans_fk as cTrans_pk, ;
>>TI.cInsurances_fk ;
>>FROM (select curTemp.cTrans_pk, max(T.iSequence_Number) iSequence_Number ;
>>from curTemp JOIN Trans_Insurances T ;
>>ON curTemp.cTrans_pk = T.cTrans_fk GROUP BY 1) X ;
>>JOIN Trans_Insurances TI ;
>>ON TI.cTrans_fk = X.cTrans_pk into cursor curTemp1
>>
>>*expected
>>Rushmore optimization level for intermediate result: none
>>Rushmore optimization level for table t: none
>>Joining intermediate result and table t using index tag Ctrans_fk
>>Rushmore optimization level for intermediate result: none
>>Rushmore optimization level for table ti: none
>>Joining intermediate result and table ti using index tag Ctrans_fk
>>
>>

>
>In other words, the AND condition on iSequence_Number makes this select much slower.

Yes, VFP merge the two and condition and build a "In fly temp index of the 400k records"

ON TI.cTrans_fk = X.cTrans_pk AND TI.iSequence_Number = X.iSequence

become

ON BINTOC(TI.cTrans_fk)+BINTOC(TI.iSequence_Number) = BINTOC(X.cTrans_pk)+BINTOC(X.iSequence_Number)

this is a bug into the query optimizer.

we now have to deceive the VFP engine

* try 1 : same ?
SELECT TI.cTrans_fk, TI.cInsurances_fk;
    FROM (select curTemp.cTrans_pk , max(T.iSequence_Number) iSequence_Number;
    	from curTemp JOIN Trans_Insurances T ON curTemp.cTrans_pk = T.cTrans_fk ;
	    GROUP BY 1) X ;
	    JOIN Trans_Insurances TI ;
            ON TI.cTrans_fk = X.cTrans_pk ;
        WHERE TI.iSequence_Number = X.iSequence_Number;
into cursor curTemp1

* try 2 : error ?
SELECT TI.cTrans_fk, TI.cInsurances_fk;
    FROM (select curTemp.cTrans_pk , max(T.iSequence_Number) iSequence_Number;
    	from curTemp JOIN Trans_Insurances T ON curTemp.cTrans_pk = T.cTrans_fk ;
	    GROUP BY 1) X ;
	    JOIN Trans_Insurances TI ;
            ON TI.cTrans_fk = X.cTrans_pk ;
        HAVING TI.iSequence_Number = X.iSequence_Number;
into cursor curTemp1

* try 3 : can to go in error if (cTrans_fk ,iSequence_Number) is not unique
SELECT TI.cTrans_fk;
  , (SELECT TI.cInsurances_fk FROM Trans_Insurances TI ;
         WHERE TI.cTrans_fk = X.cTrans_pk AND TI.iSequence_Number = X.iSequence_Number);
    cInsurances_fk;
    FROM (select curTemp.cTrans_pk , max(T.iSequence_Number) iSequence_Number;
    	from curTemp JOIN Trans_Insurances T ON curTemp.cTrans_pk = T.cTrans_fk ;
	    GROUP BY 1) X ;
into cursor curTemp1

* try 4
SELECT TI.cTrans_fk, TI.cInsurances_fk;
    FROM (select curTemp.cTrans_pk , max(T.iSequence_Number) iSequence_Number;
    	from curTemp JOIN Trans_Insurances T ON curTemp.cTrans_pk = T.cTrans_fk ;
	    GROUP BY 1) X ;
	    LEFT JOIN Trans_Insurances TI ;
            ON TI.cTrans_fk = X.cTrans_pk ;
        WHERE TI.iSequence_Number = X.iSequence_Number;
into cursor curTemp1


* try 5 
SELECT TI.cTrans_fk, TI.cInsurances_fk;
    FROM (select curTemp.cTrans_pk , max(T.iSequence_Number) iSequence_Number;
    	from curTemp JOIN Trans_Insurances T ON curTemp.cTrans_pk = T.cTrans_fk ;
	    GROUP BY 1) X ;
	    JOIN Trans_Insurances TI ;
            ON TI.cTrans_fk = X.cTrans_pk and INLIST(X.iSequence_Number,TI.iSequence_Number) ;
into cursor curTemp1