>>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
>
>Time: 3.554
>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
>
>
>>* 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
>>
>
>Time: 0.046
>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
>
>Looks like a winner!!!!!!!!!!!!
>
>>* 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
>>
>
>Gives an error alias TI is not found.
>
>>* 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
>>
>>
>Time: 0.053
>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
>
>Looks like a winner too.
>
>
>>* 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
>>

>
>Time: 0.061
>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
>
>So, basically, 2,4,5 gave result in less than 0.1 sec. I think, I'll go with 2.
>
>Thanks a lot, Fabio.
>
>(I haven't re-started between tries, so this may affect the result...)

my typo

* try 3 : can to go in error if (cTrans_fk ,iSequence_Number) is not unique and is it slow too ?
SELECT X.cTrans_pk 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

You can uses 2, but it is out of SQL standard,
5 is not readable,
4 is better.

UPDATE: ok for the 2. On a more smart backend uses the 1th.