>I'm thinking I can inner join to (select max(iSequence_Number)...) in my additional join statement. Do you know if we have a limitation for number of projections? BTW, I can not figure out how to write this inner select either.
>


this is the SQL syntax, but VFP9 don't support ON subquery

 INNER JOIN Trans_Insurances on ;
	 Trans.cTrans_pk = Trans_Insurances.cTrans_fk ;
         AND  Trans_Insurances.iSequence_Number=(select max(iSequence_Number) FROM Trans_Insurances X WHERE X.cTrans_fk = Trans_Insurances.cTrans_fk );
	 INNER JOIN Insurances on ;
	 Trans_Insurances.cInsurances_fk = Insurances.cInsurances_pk ;
	 INNER JOIN Carrier_Branches on ;
	 Insurances.cCarrier_Branches_fk = ;
          Carrier_Branches.cCarrier_Branches_pk ;
 	 INNER JOIN Carriers on ;
	 Carrier_Branches.cCarriers_fk = Carriers.cCarriers_pk

you can use ( a lot slower because it grouped Trans_Insurances fully )

 INNER JOIN Trans_Insurances on ;
	 Trans.cTrans_pk = Trans_Insurances.cTrans_fk ;
 INNER JOIN (SELECT cTrans_fk,max(iSequence_Number) iSequence_Number FROM Trans_Insurances GROUP BY 1) Last;
         ON Trans_Insurances.cTrans_fk = Last.cTrans_fk AND Trans_Insurances.iSequence_Number = Last.iSequence_Number ;
	 INNER JOIN Insurances on ;
	 Trans_Insurances.cInsurances_fk = Insurances.cInsurances_pk ;
	 INNER JOIN Carrier_Branches on ;
	 Insurances.cCarrier_Branches_fk = ;
          Carrier_Branches.cCarrier_Branches_pk ;
 	 INNER JOIN Carriers on ;
	 Carrier_Branches.cCarriers_fk = Carriers.cCarriers_pk

Because it is an INNER JOIn, another way put the filter in the WHERE.

>The SQL becomes very complex...
>


>>Hi everybody,
>>
>>I create SQL dynamically as a text and then execute it. I want to select records satisfying user's profile. The User's profile may have a filter based on the Insurance Carrier. Now, an account can have multiple insurances (Trans_Insurances records). The Trans_Insurances table has iSequence_Number field. In my select statement I want to select the account with the insurance with the greatest number in sequence (the last number in sequence).
>>
>>Now, here is my additional filter logic
>>
>>

>>if not empty(m.tcAdditionalFilter) and 'carriers' $ lower(m.tcAdditionalFilter)
>>  text to lcAdditionalJoins noshow pretext 7
>>	 INNER JOIN Trans_Insurances on ;
>>	 Trans.cTrans_pk = Trans_Insurances.cTrans_fk ;
>>	 INNER JOIN Insurances on ;
>>	 Trans_Insurances.cInsurances_fk = Insurances.cInsurances_pk ;
>>	 INNER JOIN Carrier_Branches on ;
>>	 Insurances.cCarrier_Branches_fk = ;
>>          Carrier_Branches.cCarrier_Branches_pk ;
>> 	 INNER JOIN Carriers on ;
>>	 Carrier_Branches.cCarriers_fk = Carriers.cCarriers_pk
>>   ENDTEXT
>>endif
>>

>>
>>And that's the select itself (quite complex) and I don't see a way of specifying that I need the insurance with the maximum number.
>>
>>

>>select top <<m.tnNumAccounts>> trans.cTrans_pk, ;
>>	space(16) as cCommission_Owner_UsGrLink_fk, ;
>>	space(16) as cQueue_names_fk, ;
>>	0 as iAssigned, ;
>>	step1.tScheduled_Time, ;
>>	step1.cTrans_Employees_Queues_Pk, ;
>>	step1.tExpire_date ;
>>	from trans INNER join ;
>>	(select ;
>>	NVL(EQS.tScheduled_Time, <<m.lcFutureDate>>) ;
>>	as tScheduled_Time, TEQ.tExpire_date, ;
>>	TEQ.cTrans_Employees_Queues_Pk,  ;
>>	TEQ.cTrans_fk from Trans_Employees_Queues TEQ ;
>>	LEFT JOIN Employee_Queue_Schedules EQS ON ;
>>	TEQ.cTrans_Employees_Queues_pk = ;
>>	EQS.cTrans_Employees_Queues_fk ;
>>	and EQS.iActive_Flag = 1 and ;
>>	EQS.tScheduled_Time < <<m.lcAssignDate>> ;
>>	where TEQ.cCommission_Owner_UsGrLink_fk IS NULL ;
>>	and TEQ.cQueue_names_fk = '<<m.tcQueuePk>>' ;
>>	and TEQ.iActive_Flag = 1) step1 ;
>>	on Trans.cTrans_pk = step1.cTrans_fk ;
>>	<<m.lcAdditionalJoins>> ;
>>	where <<m.lcFilter>>

>>
>>Can you please give me a hand?
>>
>>Thanks in advance.