>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...)