hI boris

>You get what you ask for :-)
>There is NO guarantee that MAX(E.Ltransfert) will be from the last record. This will give just biggest value of this field.
>How do you define the records ass LAST?


i want the record of etats.dbf where etats.iid = 4818 ( the bigst primary key )

what can i put in my sql querie to have etats.ltransfert ?
why the sql give informations from 2 records !

if i do'nt put max( etats.ltransfert ) i have a error ( group by is not valid .... )

bernhart


>>hi all,
>>
>>i want a cursor with customer informations ( here the custmer name is 'boyne' )
>>
>>i want the LAST record of children table ( Etats.dbf )
>>
>>The key between the 2 tables is a integer ( Iresidentid for etats.dbf and IID for resident.dbf )
>>
>>
>>here i have a strange result ! Please see the attachment ...
>>
>>Cor9.IetatId give a good result ( it 's the last record of etats.dbf )
>> Cor9.nType is bad result ! ( it's the first record of etats.dbf)
>> Cor9.ccat_Physiq , give a good result ( it 's the last record of etat.dbf )
>> cor9.ddebut, give a good result ( it 's the last record of etat.dbf )
>> cor9.Ltransfert; is bad result ! ( it's the first record of etats.dbf)
>>
>>
>>SET ENGINEBEHAVIOR 90 is Ok in beginning...
>>
>>What happend ?
>>
>>bernhart
>>
>>
>>

>>
>>SET ENGINEBEHAVIOR 90
>>
>> m.cfiledata = "C:\cdbk90\amline\data2\"
>> USE (  m.cfiledata + "AMLINE!Resident") IN 0
>> USE (  m.cfiledata + "AMLINE!Etats") IN 0
>> 
>>
>> SELECT R1.Cnomcustomer ,;
>>  R1.Iid ,;
>>  Cor9.IetatId  , ;
>>  Cor9.nType  ,;
>>  Cor9.ccat_Physiq , ;
>>  cor9.ddebut,;
>>  cor9.Ltransfert;
>>   FROM Amline!Resident R1, ;
>>    ( SELECT  E.iresidentId , ;
>>  MAX( NVL( E.nType, 0 )) AS nType ,;
>>  MAX( E.iid ) AS IetatId ,;
>>  MAX(E.ccat_Physiq) AS ccat_Physiq,;
>>  CAST( MAX( NVL( E.lTransfert , .F. ) ) AS L ) AS lTransfert  ,;
>>  MAX( E.ddebut ) AS ddebut;
>>  FROM Etats E GROUP BY E.iresidentId   ) AS Cor9 ;
>>  WHERE R1.iid = cor9.iresidentId ;
>>  AND  EMPTY( TTOD( R1.dsortie ) )  ORDER BY R1.Cnomcustomer INTO CURSOR temp
>>
>>
>> SELECT temp
>>
>>
>> BROWSE NOWAIT
>>
>> SELECT etats
>> SET FILTER TO iresidentid = temp.iid
>> 
>> BROWSE NOWAIT
>>
>>

>
>